# Thread: count multiple IF conditions (Excel 2003)

1. ## count multiple IF conditions (Excel 2003)

I'm lost again trying to apply multiple conditions for COUNTIF. I don't think COUNTIF works with multiple conditions but I can't figure out what else to use. I have a table of names and numbers assigned. Each name can have numbers 0-7 but these 8 numbers are grouped into 4 categories like this:
<pre>RULES: CategoryA = 0, CategoryB=1, 2, CategoryC=3, 4, 5 and CategoryD = 6, 7
dog : 4 : 5
mouse: 1 : 5
horse: 0 : 3</pre>

<pre>The count of each animal per category is :
CategoryA CategoryB CategoryC CategoryD
dog 0 0 2 0
mouse 0 1 1 0
horse 1 0 1 0</pre>

So what I need is the total count (not sum) of each occurence in its category (which has multiple members). I was trying to do something like COUNTIF(animal_range,"=1" and "=2") to count the number of times "1" and "2" appeared, for example but of course I can't use more than one criteria with COUNTIF. I assume this needs an array formula but I'm pathetic at getting those to work.

The real workbook needs a summary which totals the number of companies that fall into these categories and each category has a set of sub-categories but the summary tracks the major categories not the sub-categories.

Thnx, Deb

2. ## Re: count multiple IF conditions (Excel 2003)

=COUNTIF(Range,"1")+COUNTIF(Range,"2")+......

3. ## Re: count multiple IF conditions (Excel 2003)

Yes that was my first thought, just add up the conditons like AND statements in an SQL statement but for some reason it's counting way more than is there. I attached a stripped down version removing all confidential stuff but showing the two cols that I care about (with random data). This should be easy and I can't see the problem.

// Thnx, Deb

4. ## Re: count multiple IF conditions (Excel 2003)

<P ID="edit" class=small>(Edited by sdckapr on 14-Dec-07 13:04. Added PS)</P>In G5:
=SUMPRODUCT((\$D\$12:\$D\$40=\$E5)*(\$F\$12:\$F\$40>=1)*(\$F \$12:\$F\$40<=2))

In H5:
=SUMPRODUCT((\$D\$12:\$D\$40=\$E5)*(\$F\$12:\$F\$40>=3)*(\$F \$12:\$F\$40<=5))

In I5:
=SUMPRODUCT((\$D\$12:\$D\$40=\$E5)*(\$F\$12:\$F\$40>=6)*(\$F \$12:\$F\$40<=7))

Copy G5:I5 to G6:I9

Steve
PS if you want to be explicit about =1 and =2, etc instead of the ranges, you can use:
G5
=SUMPRODUCT((\$D\$12:\$D\$40=\$E5)*((\$F\$12:\$F\$40=1)+(\$F \$12:\$F\$40=2)))

H5
=SUMPRODUCT((\$D\$12:\$D\$40=\$E5)*((\$F\$12:\$F\$40=3)+(\$F \$12:\$F\$40=4)+(\$F\$12:\$F\$40=5)))

I5
=SUMPRODUCT((\$D\$12:\$D\$40=\$E5)*((\$F\$12:\$F\$40=6)+(\$F \$12:\$F\$40=7)))

5. ## Re: count multiple IF conditions (Excel 2003)

Yes this is exactly <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> what I needed. I had tried SUMPRODUCT but wasn't combining it correctly to do the ANDing function. I'll definitely be using this a lot for other problems.

Thanks much, Deb

6. ## Re: count multiple IF conditions (Excel 2003)

Just to be explict:
To do ANDs with the SUMPRODUCT (and other array techniques) one multiplies (ie uses an asterisk[*])

To do ORs with the SUMPRODUCT (and other array techniques) one adds (ie uses a plus [+])

MS MVP Chip Pearson has a very nice Array Formula Primer and MS MVP Bob Umlas has a nice article on Array Formulas as well.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•