Count / sum cells based on 2 criteria

[Paul Naylor]

Hi Hoping someone can help

Trying to measure downtime.
Got a list of colleague names column E
Date issue reported cilumn F
Downtime in mins column M
Managed to work out total no issues & downtime per colleague and total no issues / downtime for yesterday & today , but struggling to identify no issues and downtime for today & yesterday per colleague ( colleague names in column Q, No issues reported today column T, total downtime today column u

Formula for no issues reported by colleague column T
=COUNTIFS($F2:$F1000,"=TODAY()",$E2:$E1000,Q2) but getting #NAME?

Also need a SUM formula to sum up the total downtime for each colleague for today ( columns as per anove colleague names in column E, dates column F , downtime values column M, colleague lookup column Q

 

[etaf]

change to
=COUNTIFS($F2:$F1000,TODAY(),$E2:$E1000,Q2)

 

[Paul Naylor]

Hi Still getting #Name?. Forgot to mention that using excel 97 ( works PC).
Also will the above only catch cells which contain both today's date and names or today's date and all columns containing their names?

 

[etaf]

it will only match where a row matches todays date AND whatever you have in Q2
I dont think Countifs works with Excel 97
you do mean the 1997 versions ???? thats very old

 

[etaf]

Countifs I think came in in EXCEL version 2007

TRY
=SUMPRODUCT(($F2:$F1000=TODAY())*($E2:$E1000=Q2))

NOT Sure what functions work in Excel 97

 

[Paul Naylor]

That's great. How do I them incorporate this to display total downtime for each colleague for today column M, presume need some sort of SUMIF formula where E2:E1000 =q2 and date column F = today's date then sum downtime values for this colleague in column M

 

[Paul Naylor]

think worked it out. Just need to add *($M2:$M1000)/60 for hrs.
Thanks for all youre help, dont think I'd been able to work it out without youre support!!!!

 

[etaf]

you are welcome