How to group data in a Google Spreadsheet?

Question

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

Answer 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

Answer 2

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

Answer 3

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