New count question

Posted by Stu on May 09, 2001 6:26 PM

I have 2 columns A & B. Each cell in Column A contains text values of "A", "B", "C", "D", or "E". Each cell in column B contains either "YES" or "NO".
I need a formula that counts the number of "A", "B", and "C"s in column A if there is a corresponding "YES" in column B.

I used a pivot table and it works as far as getting the right answer, but I would prefer a formula as this template will be used by other people I don't want to have to refresh a pivot table every time the data changes.

Stu

Posted by Mark W. on May 09, 2001 6:43 PM

Fundamentally, this is same as your previous request...

You'll need 3 separate array formulas:

1. {=SUM((A1:A6="A")*(B1:B6="YES"))}
2. {=SUM((A1:A6="B")*(B1:B6="YES"))}
3. {=SUM((A1:A6="C")*(B1:B6="YES"))}

Or, 1 doozie like this:

{=SUM(ISNUMBER(MATCH(A1:A6,{"A","B","C"},0))*(B1:B6="YES"))}

Posted by Mark W. on May 09, 2001 6:50 PM

BTW, your formulas would be simplier if...

...you replaced "YES" with 1, "NO" with 0, and
the formatted the values as: [=1]"YES";[=0]"NO"

Then your formulas could be simplified to:

1. {=SUM((A1:A6="A")*B1:B6)}
2. {=SUM((A1:A6="B")*B1:B6)}
3. {=SUM((A1:A6="C")*B1:B6)}

Or,

{=SUM(ISNUMBER(MATCH(A1:A6,{"A","B","C"},0))*B1:B6)}

Posted by Stu on May 09, 2001 6:51 PM

Thanks, That was a BIG help! nm