count formula

[OKY]

Hi
I am makeing a worksheet for the dispatchers ast works.
This is what I did so far.
TECH NAME JOB# TIME HHHC STATUS
LUIS G 1R 8 - 11am fail Completed
3R 11 - 2pm
5R 2 - 5PM
Tech name, time, HHHC and status have a drop down list. their reference is
in cell AA.

So what I need the dispatch to manualy type is the Job#, anything else is a
drop down list or combo.
I need to have in sheet2 a report of all the jobs that still not completed
according to their time scheduled.. For example:

TECH NAME TIMEFRAME QTY TOTAL JOBS OPEN
LUIS G 11 - 2PM 1 2
2 - 5PM 1

I need your help ASAP if possible.
Thank you

 

[Francis]

do you only want to count the number of open items for each name?
if yes, try this in D2 of Sheet2 and copy down
=SUMPRODUCT(--(Sheet1!$A$2:$A$11=A2),--(Sheet1!$E$2:$E$11<>"Completed"))

for your example, this will return 2 for the name LUIS G

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[OKY]

Actually I want to count the number of jobs not completed, the tech name and
time..
For example Luis G has 2 open jobs , one from 11-2pm and another 2-5pm.