Search code examples
google-sheetsgoogle-sheets-formulauniquearray-formulas

Count unique rows with a criterion of multiple columns

The formula in B2:

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

enter image description here

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

Solution

  • 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)