180

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

3 Answers 3

209

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

example formula output

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

2
57

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

0
12

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

enter image description here

2
  • 2
    Nice answer with image, but why not edit the previous answer and just add the image?
    – vinnief
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.