Count conditions (2003)

Topic

New Reply

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

Reply | Quote

Replies

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”))

Reply | Quote

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

Nathan

Reply | Quote

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

Reply | Quote

You have to use SUMPRODUCT again:

=SUMPRODUCT((DEBITS!A3:A65536="XXX")*(DEBITS!P3:P65536="XXX")*DEBITS!F3:F65536)

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)

Reply | Quote

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?

Reply | Quote

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…

Reply | Quote

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.

Reply | Quote