# Alternative way to do multiple COUNTIF

December 15, 2010 at 05:42:53
Specs: Windows XP
 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 DAVISCOLUMD 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.

See More: Alternative way to do multiple COUNTIF

#1
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, ...)MIKEhttp://www.skeptic.com/

Report •

#2
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.....MIKEhttp://www.skeptic.com/

Report •

Related Solutions