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
-
