CountIfs using or statement, not duplicating counts

[soozler]

The Spreadsheet:

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: ?

 

[Marcelo Branco]

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.

 

[Jeff Meyers]

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

Sheet1

	A	B	C	D
1	Name	Sausage	Pepperoni	Cheese
2	Joe	x	x	x
3	Frank
4	Ben		x
5	Kevin
6	Collin	x		x
7
8
9	----------------	----------------	----------------	----------------
10
11	3
12

<thead>
</thead><tbody>
</tbody>

Excel 2010

Worksheet Formulas Cell Formula A11 =SUMPRODUCT((A2:A6<>"")*(B2:B6&C2:C6&D2:D6<>"")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[T. Valko]

The Spreadsheet:

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.