MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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:

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

You can do this with a "CSE" array formula - Look at the tips in the Mr Excel front page.
The formula would probably be:
and hold down CTRL and SHIFT when pressing enter.
Make sure youput the XXXXX inside "" marks.

Posted by Aladin Akyurek on July 03, 2001 12:24 AM

Multiconditional Count


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.