# How to group data in a Google Spreadsheet?

I have the following table in a Google Spreadsheet:

``````Name | Value
A    | 10
B    | 100
A    | 20
B    | 200
C    | 1000
``````

How can I create another table based on a query:

``````select name, sum(value), count(value) from table group by name
``````

so it looks like this:

``````Name | SUM   | COUNT
A    | 30    | 2
B    | 300   | 2
C    | 1000  | 1
``````

By using the `QUERY` function you can achieve your goal.

## Formula

``````=QUERY(Data!A1:B6;"select A, sum(B), count(B) group by A")
``````

## Screenshot

Using other functions, makes it achievable as well.

add this formula in A2:

``````=UNIQUE(Data!A2:A)
``````

add the following formulas in B2 and C2 and copy down to B4 and C4 respectively

``````=SUM(FILTER(Data!\$B\$2:B;Data!\$A\$2:A=A2))
=COUNTA(FILTER(Data!\$B\$2:B;Data!\$A\$2:A=A2))
``````

## Example

I've made an example file for you: QUERY vs. FUNCTIONS

You can use Pivot Tables to group data in Google Spreadsheets.

Why not using Pivot Table? It will give you more options and controls than writing a custom formula. Controls live Add Columns, Rows, Values, Filters

• Nice answer with image, but why not edit the previous answer and just add the image? Jan 10, 2020 at 11:37
• Thank you. I'm not happy to click through to a blog to get my answer so this is my preferred answer. Oct 10, 2022 at 20:40