Excell Countif w/ more than one criteria

  • Thread starter Thread starter SKelly
  • Start date Start date
S

SKelly

I need to count how many time a selection come up in my log.

Example:
Tab 1: Log
D E D
1 charge code WR Code Sub. code
2 HR Admin Miscellaneous
3 Facility Admin Miscellaneous
4 Facility Employees New Issue
5 Facility Temp New Issue
6 Facility Temp Miscellaneous

Tab 2: Weekly Report
1 Totals: Formula I used:
2 HR =COUNTIF(Log!D:D,"HR")= 1 good
3 Facility =COUNTIF(Log!D:D,"Facility")= 3
4 Blank (two sections)
5 -Admin =COUNTIF(Log!E:E,"Admin")= 2 good
4 -Miscellaneous =(?) I need only the count that see both
Miscellaneous and Temp to =1 How (If D is Miscellaneous and E is Temp then
the count is 1)

I tryied =countif(D:D,"Miscellaneous",E:E,"Temp") didn't work. Can you help?
 
Use SUMPRODUCT.

=SUMPRODUCT(--(D1:D100="Miscellaneous"),--(E1:E100="Temp"))

You have to specify the range with this. It cannot be an entire column.

Does that help?
Paul
 
what is "--"?
--
Susan Kelly


PCLIVE said:
Use SUMPRODUCT.

=SUMPRODUCT(--(D1:D100="Miscellaneous"),--(E1:E100="Temp"))

You have to specify the range with this. It cannot be an entire column.

Does that help?
Paul
Click to expand...
 
This is a double urnary or double minus. It converts true/false to 1's and
0's. So each time it matches "Miscellaneous", it returns true and is
coverted to 1. Same thing goes with "Temp". So if D1 returns True and E1
returns "False", then that converts to 1 and 0. Because this is SUMPRODUCT,
the two are multiplied together. 1 * 0 = 0. But if both D1 and E1 return
true, thats 1 and 1. 1 * 1 = 1. So each time the criteria is matched as
desired, it will count as one.

Hope this helps.
Paul

--
 
Back
Top