# How to group data (by row and column) in a Google spreadsheet?

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.

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")`

• If this was your solution please mark your answer and explain how it performs the task better than suggested answers (we are looking through a microscope at a small sample of what you are trying to do) – user205834 Jan 15 '19 at 20:26
• I can't mark within 24h. The question specifies that the solution should be for an arbitrary number of columns and rows. This is the only solution doing so (atm). – user1527152 Jan 16 '19 at 13:56

``````=QUERY(QUERY(QUERY(
{QUERY(Sheet1!A2:E, "select A, count(C) where C='Y' group by A label count(C)''", 0),
QUERY(Sheet1!A2:E, "select A, count(E) where E='Y' group by A label count(E)''", 0)},
"select *", 0),
"select Col1, sum(Col2)+sum(Col4) group by Col1 label sum(Col2)+sum(Col4)'COUNT'", 0),
"select Col1, Col2 label Col1'Name'")``````

• Thank you for this answer but as I mentioned there are many columns and cannot hard-code it for all columns in my spreadsheet. The example is a simple sample of my data. – user1527152 Jan 14 '19 at 22:23
• @user1527152 well this is the way. put those many columns in array `{}` and then sum them on 5th row like `sum(Col2)+sum(Col4)+sum(Col6)+sum(Col8)....` – user0 Jan 14 '19 at 22:30
• I have 100+ columns meaning I would have to write QUERY(...) that many time – user1527152 Jan 14 '19 at 23:00
• @user1527152 go for it :) unfortunately there isnt better solution (unless you will write yourself custom script) – user0 Jan 14 '19 at 23:05