0

I would like to return the count of the number of rows for which ANY of the columns have the number 1. The table is three columns, and each column can contain either 0, 1, 2, or 3. Example:

Col1 | Col 2 | Col 3
0 | 1 | 0
1 | 2 | 3
0 | 0 | 0
3 | 1 | 1
2 | 2 | 2
etc.

I would like the formula to return 3.

pnuts
  • 58,317
  • 11
  • 87
  • 139
user3417733
  • 13
  • 3
  • Use an extra column to store intermediate values, and countif on that. Complex formulas in Excel are difficult to maintain and debug. – Bathsheba Sep 15 '15 at 19:57

1 Answers1

0

Here you go:

=SUMPRODUCT(--((A1:A10=1)+(B1:B10=1)+(C1:C10=1)>0))

Change the 10s to however far down you need to go.

Excel Hero
  • 14,253
  • 4
  • 33
  • 40