MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Counting data occurances in different columns


Posted by Dom Parker on December 22, 2001 12:29 PM

I have a 1000 line spreadsheet and need a formula that will count the number of times that "yes" appears in columns 3, 5 & 7 on the same line. I can calculate it on an individual line but the countif formula will not accept ranges. Can anyone help ? Great if you can as I am tearing my hair out on this one. Thanks : Dom


Posted by Aladin Akyurek on December 22, 2001 1:21 PM

Dom --

=SUMPRODUCT(C1:C100="Yes")*(E1:E100="Yes")*(G1:G100="Yes"))

will give you the count you want. Adjust the ranges to your situation.

Aladin

===========

Posted by Dom Parker on December 23, 2001 1:15 AM


Aladin,
Many thanks for this : However, when I enter your suggested solution, excel suggests that I remove the final ) and then reports a "0" result (even though there are instances with "yes" in every column. I have checked this and excel suggests that text is treated as "0". I feel I am very close with this so please could you just clarify you advice. I am really grateful for your assistance : Regards : Dom

Posted by Aladin Akyurek on December 23, 2001 1:39 AM

Dom --

My mistake. I forgot a paren that is necessary:

=SUMPRODUCT((C1:C100="Yes")*(E1:E100="Yes")*(G1:G100="Yes"))

Aladin

========

Posted by Dom Parker on December 23, 2001 5:11 AM

Aladin,
Just a short note to say thanks very much, you have just saved me hours of work and struggle and I have also learnt something new. Please accept my thanks, your solution works well and is much appreciated : Regards : Dom
-