How to do a little math in the criteria_range of Countifs Functions (using OR in Countif)

Question

The Excel File is like this

A  B
1  0
0  1
1  1
0  1
0  0
1  0

I want to use Countifs function to count how many rows have at least one "1" in any columns, like

=Countifs(A:A+B:B,">=1")

or

=Countifs(or(A:A=1,B:B=1))

I know I can add a Column C, let Column C = Column A + B, and then just count Column C; or I can count the total rows and count rows with "0" in both columns, and then calculate Total Row - Both "0". But in real Scenario, I have more complicated situation, so I prefer not using these two solutions.

Answer 1

Use =COUNT(A:A)-COUNTIFS(A:A,0,B:B,0) to count both 0 columns and subtract it from the total rows:

Or you can use:

=COUNTIFS(A:A,1,B:B,1)+COUNTIFS(A:A,0,B:B,1)+COUNTIFS(A:A,1,B:B,0)

if it is not clear what it the total number of rows.

Answer 2

Another solution using array formula:

=SUM(IF(A1:A6=1,1,IF(B1:B6=1,1,0)))

Being an array formula, you'll have to enter this formula by pressing Ctrl+Shift+Enter together.

Answer 3

Use a SUMPRODUCT function to provide cyclic calculation.

=SUMPRODUCT(--((A1:A6)+(B1:B6)>=1))

SUMPRODUCT does not like trying to calculate text values and full column references slow it down so keep your ranges to a minimum. Using the INDEX function can help isolate a dynamic range of true numbers.