How to count rows that have same value in Excel

[Robertislearning]

Hi!

I currently have a 400+ line report similar to this:

Jan Feb Mar
Emp A X X X
Emp B A X A
Emp C X X X

I'm looking for a formula that will count all rows that have an "X" all the way across (B2, C2 & D2).

I was going for something like =countif(B2:C4,"X") but this counts the total number of X's (7). I'm looking for something that will give me a total of 2 (two rows with X's all the way across) in the example above.

Thanks in advance for your help!

Robert.

 

[Bowers74]

This would work for your example data, although there might be a different way:

=SUMPRODUCT((B2:B4="X")*(C2:C4="X")*(D24="X"))

I hope this helps!


Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[sawedoff]

Use the Conditional Sum wizard. Add all the columns as rules, then sum one of the columns for your total count.

Sawedoff

 

[Corgano]

Brute force method:

In Column D: =If(countif(B2:B4,"X")=3,1,0)

Where "3" is the number of "X" you want. Paste the formula all the way down and Sum column D for the number of hits.