# Thread: Count conditions (2003)

1. ## Count conditions (2003)

Hi,
Can a count be done based upon 2 conditions? eg:
COUNT IF A:A="X" and IF B:B="Y"
Got my stoopid brain in tonight!!
Thanks
Nathan

2. ## Re: Count conditions (2003)

You can use SUMPRODUCT for this, but you have to specify a "finite" range instead of entire columns. For example:

=SUMPRODUCT((\$A1:\$A10000="X")*(\$B1:\$B10000="Y"))

3. ## Re: Count conditions (2003)

Perfect! Thanks Hans, that is not a function that I have ever used before.

Nathan

4. ## Re: Count conditions (2003)

Is there a similar function for sum? Only I can't get this to work.
SUMIF(AND(DEBITS!A3:A65536,"XXX"),(DEBITS!P3:P6553 6,"XXX")),DEBITS!F3:F65536)
Thanks

5. ## Re: Count conditions (2003)

You have to use SUMPRODUCT again:
<code>
=SUMPRODUCT((DEBITS!A3:A65536="XXX")*(DEBITS!P3:P6 5536="XXX")*DEBITS!F3:F65536)
</code>
Note: Excel 2007 introduces new functions COUNTIFS, SUMIFS and AVERAGEIFS that let you specify multiple conditions. In Excel 2003 and before, you have to use SUMPRODUCT (or a combination of SUM and IF in an array formula)

6. ## Re: Count conditions (2003)

Thanks Hans, perfect again!

I'm using Excel 2003. I can use SUMIF and COUNTIF for single conditions but not multiple conditions? It may be an add in that I have?

7. ## Re: Count conditions (2003)

SUMIF and COUNTIF only let you specify a single condition. If you search, you'll find numerous threads here and in other discussion forums about how to calculate sums and counts with multiple conditions. In Excel 2007, Microsoft finally added new functions.

I obviously don't know which add-ins you have installed...

8. ## Re: Count conditions (2003)

Sorry Hans, After re-reading your reply, I have noticed that you were referring to sumifS, countifS, which I now see are different to plain sumif and countif. I had'nt realised. Thanks again.

#### Posting Permissions

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