Hi, 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.
Basically...
COLUMN C is one of 3 names; e.g. J SMITH, J BLOGGS, D DAVIS
COLUMD D is a priority; e.g. High, Medium, LowI 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.
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, ...)
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 lowTry 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.....
Ad Choices