MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Counting


Posted by Stu Davis on May 09, 2001 4:11 PM

I have 2 columns, Column A and Column B that each contain "YES" or "NO". What formula will count the number of "yes" in column A if there is a corresponding "yes" in column B.

Thanks in advance for your help.


Posted by Ivan Moala on May 09, 2001 4:27 PM

Array enter =SUM((A1:A6="YES")*(B1:B6="YES"))

ie enter as Ctrl + Shft + Enter
See mrexcel tip on CSE formulas

Ivan

Posted by Stu on May 09, 2001 4:50 PM


I tried that array formula and it returns 0 for the result. Any other suggestions?

Thanks,
Stu

Posted by Mark W. on May 09, 2001 5:30 PM

Is your formula bracketed by curly braces, {}, as
show below?

{=SUM((A1:A6="YES")*(B1:B6="YES"))}

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

Yes, I used Ctlr, Shift, Enter to enter the formula and got the curly braces.

Stu

Is your formula bracketed by curly braces, {}, as

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

Make sure that you don't have "YES " in columns
A:B. A quick way to make sure would be to
select A1:A6 and choose the Data | Text to Columns...
menu command, click the "Fixed width" radio button
and then press [ Finish ]. Do the same for B1:B6.
Does the formula work now? Yes, I used Ctlr, Shift, Enter to enter the formula and got the curly braces. Stu

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

Thanks! That worked.

Stu

Make sure that you don't have "YES " in columns