GROUP BY with aggregation in Google Sheets - Step-by-Step Guide

Introduction

Grouping data helps you see summaries like totals or averages for each category. This is useful when you want to combine rows that share the same value and calculate something for each group.

When you want to see total sales for each product category in your sales list.

When you need to find the average score for each student from a list of test results.

When you want to count how many orders each customer made from your order data.

When you want to sum expenses by month from a list of daily expenses.

When you want to group survey responses by answer choice and count how many chose each.

Steps

Step 1: Click

- an empty cell where you want the grouped result

The cell is selected and ready for formula input

Step 2: Type

- the selected cell

You start entering the formula

💡 Start with =QUERY to use grouping and aggregation

Step 3: Enter

- the formula bar

The formula is ready to run

💡 Use syntax like =QUERY(A1:C10, "select A, sum(B) group by A", 1)

Step 4: Press

- Enter key

The grouped and aggregated data appears in the sheet

Step 5: Check

- the output cells

You see each unique value from the group column with the aggregated result next to it

Before vs After

Before

A list of sales with columns: Product, Quantity, Price. Multiple rows have the same Product name.

After

A summary table showing each Product once with the total Quantity sold for that product.

Settings Reference

Query range

📍 First argument of QUERY function

Defines the data to group and aggregate

Default: None

Query string

📍 Second argument of QUERY function

Defines how to group and what to calculate

Default: None

Headers

📍 Third argument of QUERY function

Specifies how many header rows the data has

Default: 1

Common Mistakes

Forgetting to put the query string in quotes

Google Sheets will show an error because the formula syntax is incorrect.

Always put the query string inside double quotes, for example: "select A, sum(B) group by A"

Not matching the group by columns with the select columns

The QUERY function requires that all columns in select that are not aggregated must be in group by.

Include all non-aggregated columns in the group by clause exactly as in the select clause.

Using aggregation functions on text columns

Aggregation functions like sum or avg only work on numbers, not text.

Apply aggregation only on numeric columns, and group by text columns.

Summary

Use the QUERY function to group data by one or more columns and calculate totals, averages, or counts.

The query string must include select, aggregation functions, and group by clauses correctly.

Always put the query string in double quotes and match group by columns with selected non-aggregated columns.