Count unique rows with a criterion of multiple columns

Question

The formula in B2:

=COUNTIF(FILTER(C3:D, C3:C=TRUE, D3:D=TRUE), TRUE)

• Should count how many rows have at least one TRUE value in either column C or D (in the picture the checkboxes in the range C3:C5, D5 are TRUE).
• In the case of a row with both columns TRUE it should still count the row as 1.

But this formula counts a row only if in both columns C and D there's a TRUE value and in that case the row is counted as 2.

.

I have also tried

=FILTER(C3:D, OR(C3:C=TRUE, D3:D=TRUE))

but gives the error: #N/A FILTER has mismatched range sizes. Expected row count: 50498, column count: 1. Actual row count: 1, column count: 1.

What formula can I use in this case?

Answer 1

The FILTER function performs an AND between the conditions, to simulate an OR you can add them instead.

=ROWS(FILTER(C3:D,C3:C+D3:D))

Note that the =TRUE is redundant since the values are already booleans.

You could also use SUMPRODUCT, like this:

=SUMPRODUCT(C3:C+D3:D>0)

Answer 2

You may try:

=countif(index(C3:C+D3:D),">0")