Excel: Formula to count rows 2

[Robertislearning]

Hi! I have the following sample data in a spreadsheet:


A B C
Sue Yes Yes Yes
Ann No No No
Joe X X X
Bob X X X
Kay Yes Yes Yes


I'm trying to figure out a formula that will give me the total number of people who have X's across all columns in a given range. In the example above, I would need a formula to give me a total of 2 (both Joe & Bob have X's across all columns).

Thanks in advance!

Robert.

 

[SkipVought]

Hi,

If Column A contains Names and column B thru D contains values then

Column E need a formula
[tt]
=COUNTIF(B11,"X")
[/tt]
then the formula is
[tt]
=Countif(E:E,3)
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Robertislearning]

Hi SkipVought,

Thank you very much for your suggestion. I believe that this will work. I've been playing with it and I believe I need only one more tip. What is the syntax for selecting individual cells? For example: In the sample below, how would I write the formula you suggested =COUNTIF(B11,"X")so that it only looks at cells B2 & D2 & E2? I toyed with =COUNTIF(B2,D2,E2,"X") but I received errors.

A B C D E
Sue Yes Yes Yes Yes
Joe X Z X X


Thanks!

 

[SkipVought]

How 'bout
[tt]
=COUNTIF(B2:E2,"X")-COUNTIF(C2,"X")
[/tt]
???



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[bluedragon2]

Just for grins, how about:

=SUMPRODUCT((B1:B1000="X")*(C1:C1000="X")*(D11000="X"))



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

All kinds of ways to skin a cat,

'cept if yer AT! ;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Robertislearning]

Thank you two very much for your time. It's working! I sincerely appreciate your efforts.


Robert.


(I thought there was a place for me to flag this as a very hepful tip. Perhaps stars or something. I'm unable to find a place to flag this post. Either way, thank you two very much!)

 

[xlbo]

RobertisLearning - just click on the
Thank X for this valuable post
link at the bottom of the appropriate reply

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question