Alternative way to do multiple COUNTIF

December 15, 2010 at 05:42:53
Specs: Windows XP

I'm scratching my head on a problem, I wanted to use multiple criteria in a COUNTIF formula but it seems I can't... I've tried using SUMPRODUCT, but as I'm using text, not numbers it appeared to get confused.


COLUMN C is one of 3 names; e.g. J SMITH, J BLOGGS, D DAVIS
COLUMD D is a priority; e.g. High, Medium, Low

I wanted to count how many of each priority are allocated to each of the names in Column C... E.G how many 'highs' does J SMITH have, how many mediums does 'J SMITH' have etc.

Any ideas as I'm really stuck and I've tried googling but they all say use SUMPRODUCT and I can't get that to work.

See More: Alternative way to do multiple COUNTIF

Report •

December 15, 2010 at 06:05:47
If your using Excel 2007 there is a =COUNTIFS() function, note the trailing S,
It allows multiple ranges.

=COUNTIFS( Criteria_range-1, Criteria-1, Criteria_range-2, Criteria-2, ...)


Report •

December 15, 2010 at 06:24:24
For a =SUMPRODUCT() solution if your data looks like this:

     A            B      C        D        E
 1) J Smith      high         Q. Public   high
 2) R Smith      high			
 3) J Jones      high			
 4) Q. Public    high			
 5) J Smith      med			
 6) R Smith      med			
 7) J Jones      med			
 8) Q. Public    med			
 9) J Smith      low			
10) R Smith      low			

Try this: =SUMPRODUCT(--($A$1:$A$31=$D1),--($B$1:$B$31=E1))

With TEXT you must be careful that what your looking for is an EXACT match for what you have listed.
Note the period after the letter Q
If you omit it, then you get ZERO for an answer.

So that Q. Public and Q Public are NOT the same.....


Report •

Related Solutions

Ask Question