CountIf 2 Variables
Posted by Michael on July 02, 2001 6:43 PM
I have 2 columns - Column A has 1's and 0's and Column B either has "XXXXX" or is blank:
A B
1 XXXXX
0
1
0 XXXXX
What is the formula to count all instances in Column B where XXXXX occurs AND in the corresponding row under Column A a "0" appears?
Posted by Chris B on July 02, 2001 7:37 PM
Michael,
You can do this with a "CSE" array formula - Look at the tips in the Mr Excel front page.
The formula would probably be:
=sum(if($a$1:$a$55=0,if($b$1:$b$55-"XXXXX",1,0)))
and hold down CTRL and SHIFT when pressing enter.
Make sure youput the XXXXX inside "" marks.
regards,
Chris
Posted by Aladin Akyurek on July 03, 2001 12:24 AM
Multiconditional Count
Michael,
COUNTIF cannot have but a single condition. In case there are more conditions that determine the count that we want we can use an array formula.
Lets say your data are in A2:B7 and consist of
{1,"XXXXX";0,"";1,"";0,"";0,"";1,"XXXXX"} [ "" means blank ]
In C2 array-enter: =SUM((A2:A7=1)*(B2:B7="XXXXX")) [ Hit CONTROL+SHIFT+ENTER, whence CSE, at the same time (not just ENTER) to enter this formula ]
How this formula works? One way to explain it is at the same time how one can avoid using it with huge amount of data.
In D2 enter: =(A2=1)*(B2="XXXXX") [ Note that this is an ordinary logic formula; It will produce a 1 or a 0, because of A values that are numbers. 1 means TRUE which boils down to the fact that we have in A2 a 1 and in B2 an "XXXXX". ]
Copy down this formula as far as needed. In D2:D7 we have now a run 1's and 0's. The array formula suggested above produces this run in a single spot which gets then summed by SUM.
In C1 enter: =SUM(D2:D7) or =COUNTIF(D2:D7,1)
The last method would work faster with say 10000 rows of data but will consume more memory.
Aladin
===============
