MrExcel Publishing
Your One Stop for Excel Tips & Solutions


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:


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.

OzGrid Business Applications