# CountIfs using or statement, not duplicating counts

#### soozler

##### New Member

 Name Sausage Pepperoni Cheese Joe x x x Frank Ben x Kevin Collin x x

<tbody>
</tbody>

The Problem:

I need to count how many people have at least 1 "x" in a column. The formula should return "3".

If a person has no X in the Sausage, Pepperoni, or Cheese column they are not counted.

Solutions: ?

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Assuming data in A1:D6 (headers in row 1), maybe

=SUMPRODUCT(--(COUNTIF(OFFSET(\$B\$2:\$D\$6,ROW(\$B\$2:\$D\$6)-ROW(\$B\$2),0,1),"x")>0))

M.

Here's another approach. Note that any character in the sausage / pepperoni / cheese columns will suffice for this approach (not just x's).

Sheet1
ABCD
1NameSausagePepperoniCheese
2Joexxx
3Frank
4Benx
5Kevin
6Collinxx
7
8
9----------------------------------------------------------------
10
113
12

</tbody>
Excel 2010

Worksheet Formulas
CellFormula
A11=SUMPRODUCT((A2:A6<>"")*(B2:B6&C2:C6&D2:D6<>""))

</tbody>

<tbody>
</tbody>

 Name Sausage Pepperoni Cheese Joe x x x Frank Ben x Kevin Collin x x

<TBODY>
</TBODY>

The Problem:

I need to count how many people have at least 1 "x" in a column. The formula should return "3".

If a person has no X in the Sausage, Pepperoni, or Cheese column they are not counted.

Solutions: ?
Try this...

=SUMPRODUCT(--(MMULT(--(B2:D6="x"),{1;1;1})>0))

The array constant {1;1;1} represents the number of columns in the range of interest.

Replies
2
Views
347
Replies
1
Views
417
Replies
2
Views
207
Replies
5
Views
448
Replies
8
Views
410

1,203,460
Messages
6,055,556
Members
444,796
Latest member
18ecooley

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back