COUNTIF help PLEASE!!
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.
Aladin
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.
OzGrid Business Applications
