count formula
 

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

count formula
 

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" wrote:

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

count formula
 

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.


"Francis" wrote:

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" wrote:

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