How to Group and Summarize Data in Excel (Without a PivotTable)
Sometimes you don't want a whole PivotTable — you just want total sales by region, or average score by team. Here's the formula way, and a one-click way that also does median and weighted averages.
Last updated: August 2026
| Region | Sale | Summary | |
|---|---|---|---|
| West | 200 | → | West · 2 rows · 500 |
| East | 150 | → | East · 1 row · 150 |
| West | 300 |
Method — SUMIF, COUNTIF, AVERAGEIF
- Copy the category column elsewhere and run Remove Duplicates to get the distinct groups.
- Beside each group:
=SUMIF(A:A, E2, B:B)for totals,=COUNTIF(A:A, E2)for counts,=AVERAGEIF(A:A, E2, B:B)for averages. - For a weighted average you drop to
=SUMPRODUCT((A:A=E2)*B:B*C:C)/SUMIF(A:A,E2,C:C)— correct, but a mouthful.
The 1-click way — Tellsheet
Group & Summarize gives you the PivotTable basics without the PivotTable. Pick the group-by column and how to summarize — count, sum, average, median, weighted average, min, max, or distinct count — and it writes a clean summary table to a new sheet you can sort, filter, and chart. Median and weighted average aren't native to PivotTables; here they're a dropdown. Works on Windows, Mac, and the web. Pairs with Instant Dashboard and Remove Duplicates.
Frequently asked questions
How do I summarize without a PivotTable?
Unique categories via Remove Duplicates, then SUMIF/COUNTIF/AVERAGEIF — or Group & Summarize in a couple of clicks to a new sheet.
Why not just use a PivotTable?
PivotTables need setup and refreshing and can be awkward on the web. For a quick sum/average by category, a plain summary table is faster and easier to reuse.
Can I do a weighted average by group?
Not natively in a PivotTable. Group & Summarize has a weighted-average option — choose a value column and a weight column.
Does it change my original data?
No — the summary goes to a new sheet; your source table is untouched.
Related Excel guides
Summarize by group in one click
Group & Summarize builds a clean sum/average/median-by-category table on a new sheet — no PivotTable to manage.
Get Tellsheet free See pricing