Posted by John Meehan on January 05, 2001 10:08 AM

I'm sure this is simple. Any help would be appreciated.
I'm trying to count the number of rows where A1=X and B1=Y. Thank you for your help

Posted by John Meehan on January 05, 2001 10:11 AM

Just to clarify - I meant the entire Column A and B, not just the cells.

Posted by cpod on January 05, 2001 10:55 AM

Try this:

{=SUM((\$A\$1:\$A\$100="x") * (\$B\$1:\$B100= "y"))}

This is an array function. You must press Control + Shift + Enter to enter it.

Posted by Aladin Akyurek on January 05, 2001 2:58 PM

Don't attempt though to replace the ranges in cpod's array formula (see below in this thread) by A:A and B:B or by \$A\$1:\$A\$65536 and \$B\$1:\$B\$65536 in order to cover the entire colums A and B. The formula then results in #NUM! error! Does anybody know why this is the case or should be the case.

Posted by Dave on January 05, 2001 4:48 PM

According Microsoft arrays a limited to 65535 I have no idea why, the mind boggles :O)

You can do this with Entire columns by using the DCOUNT. something like below:

=DCOUNTA(B:C,1,E1:F2)

Where B:C are the Columns to count
1 is the Column to count (could also be 2)
E1:F2 contain the Column headings and below them the x and y.