Google-sheets – How to group data (by row and column) in a Google spreadsheet

formulasgoogle sheetsgoogle-sheets-query

I have data as follows:

Name | Value | Y/N | Value | Y/N
A    | 10    |  Y  | 12    |  N
B    | 100   |  Y  | 120   |  N 
A    | 20    |  Y  | 10    |  Y
B    | 200   |  N  | 120   |  Y
C    | 1000  |  Y  | 12    |  Y

And I would like to have a condensed view of this data which would look like this:

Name | COUNT
A    | 3
B    | 2
C    | 2

I found how to do it for one column with the QUERY function:

=QUERY(Sheet1! A2:C1000,"select A, count(C) where C='Y' group by A")

However, I don't know how to repeat this for all the columns in my spreadsheet.
The example is simple and the data spreads both in rows in columns (in the same pattern), so I cannot hard-code it for the column.

Maybe I would have to do that in two steps, I'm looking for a suggestion or idea how to do that.

Best Answer

So I finally found how to do it.

I first grouped by column via the formula =COUNTIF(Sheet1!1:1, "Y") (it needs to be expended to the range desired)

which result in (on the original example):

Name | COUNT
A    | 1
B    | 1
A    | 2
B    | 1
C    | 2

Note: to also have the column name just do =A:A

Then, I used a pivot table and added the column Name and Count

Note: it also works with =QUERY(A2:B1000,"select A, count(B) group by A")

Related Topic