Search code examples
google-sheetssumgoogle-sheets-formulaarray-formulasgoogle-sheets-query

Aggregating by columns and then by rows in Google Sheets without adding columns

I would like to apply an aggregation function to columns in each row and then apply another aggregation to the results without adding calculation only column to the table. Is it possible to do it?

For example, consider the following table:

    A      B         C         D
1 Items Property1 Property2 Property3
2 Item1    Y                   Y
3 Item2                        Y
4 Item3              Y
5 Item4    Y         Y         Y

Here Y(yes) means that the item has the given property.

I would like to calculate the number of items with at least 2 properties. It should be 2 (Item1 and Item4) in this case. I can add the column E to the table like:

Number of properties
COUNTIF(B2:D2, "Y")
COUNTIF(B3:D3, "Y")
COUNTIF(B4:D4, "Y")
COUNTIF(B5:D5, "Y")

and the apply COUNTIF(E2:E5, ">2") to it. But that requires to add a column to the table that I want to avoid.

As I hack I ended up using MMULT like:

=ARRAYFORMULA(COUNTIF(MMULT({if(B2:D5<>"", 1, 0)}, {1;1;1}), ">=2"))

Here the formula replaces Y and spaces with 1 and 0, then count number of 1 in each row using matrix multiplication and then count number of cells in the resulting row with at least 2 properties. But that feels like an ugly hack that just happens to work in my case.

Is there a general way to do such double aggregation by columns and then by rows with independent aggregation functions?

Solution

  • actually, your formula:

    =ARRAYFORMULA(COUNTIF(MMULT({IF(B2:D="Y", 1, 0)}, {1;1;1}), ">=2"))

    is on the spot unless you prefer to avoid MMULT due to some unmentioned reason...

    an alternative could be:

    =ARRAYFORMULA(COUNTIF(LEN(SUBSTITUTE(
 TRANSPOSE(QUERY(TRANSPOSE(B2:D),,99^99)), " ", )), ">=2"))

    0