MrExcel Publishing
Your One Stop for Excel Tips & Solutions

COUNTIF with multiple criteria


Posted by Jim on February 06, 2002 8:36 PM

This probably involves an array formula that is still a bit over my head. Assume A1:Z1 cells are either blank or they contain 1 or 2 character entries and A2:Z2 contain time entries formatted in hours:minutes or are blank. I want a count of cells A1:Z1 where the cell = either "A" or "PD" AND the corresponding cell in row 2 is either blank or contains a value >= 3:45.


Posted by Aladin Akyurek on February 06, 2002 10:59 PM


=SUMPRODUCT(((A1:Z1="A")+(A1:Z1="PD"))*((A2:Z2>="03:45"+0)+(LEN(A2:Z2)=0)))

or

=SUMPRODUCT(((A1:Z1=A3)+(A1:Z1=A4))*((A2:Z2>=A5)+(LEN(A2:Z2)=0)))

where A3 houses "A", A4 "PD", and A5 3:45.

=============

Posted by Jim on February 07, 2002 7:35 AM

Perfect, thanks Aladin