Totals per group with group by
PremiumIn E1, use QUERY to total the Amount for each region.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Rep | Amount | |||
| 2 | North | Ann | 120 | |||
| 3 | South | Bo | 80 | |||
| 4 | North | Cy | 200 | |||
| 5 | West | Di | 50 | |||
| 6 | South | Ed | 150 | |||
| 7 | ||||||
| 8 |
Premium lesson
Unlock QUERY and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Next with premium:ARRAYFORMULASPLIT and JOIN
Need help?
Why this works
group by A makes one row per region, and sum(C) adds up the amounts in each. The groups come out sorted by region, and the header of the total column is "sum Amount"; add label sum(C) 'Total' to the query to rename it.
Common mistake: Selecting a column that is not in group by, like select A, B, sum(C) group by A. Every plain column must be grouped.
The answer
- E1
=QUERY(1A1:C6,2"select A, sum(C) group by A")
In plain words: From 1the table in A1:C6, 2list each region with the total of its amounts.