Group and count in Excel
Upload your sheet, pick the column to group by and download how many rows there are of each value, with the option to sum or average a second column.
What it is for
It is the everyday question: how many orders per country? How many customers per rep? How much does each category bill? The answer is a pivot table, but building one for a single count is more ceremony than it deserves: insert, drag fields, choose the summary function and format it.
Here you pick the column to group by, optionally the one you want to sum or average, and download the result. The most-asked pivot table, solved in a form.
How grouping works
You name the column to group by — country, rep, category, status — and the tool gathers all the rows that share each value and counts them. The result has one row per distinct value and a "Count" column with how many rows fall under each.
Rows whose grouping value is blank are not discarded: they gather into a
group called (vacío), so the total still adds up and you can see at once how
many rows arrived without that value.
Adding a measure: sum, average, min or max
Counting rows is often enough, but you frequently want a figure too: not just how many orders per country, but how much they add up to. For that you name a measure column and the function to apply:
- Sum — the total of the measure in each group (sales per country).
- Average — the mean value (average ticket per rep).
- Min and max — the smallest and largest value of each group.
The measure is computed only over the numeric cells in the group; if a group has no number at all in that column, its measure cell is left blank instead of showing a misleading zero.
How to sort the result
You can sort the groups three ways:
- By count, highest first (the default) — the most frequent on top, ideal for seeing at once which category dominates.
- By count, lowest first — to hunt down the rare or thinly populated cases.
- By label — alphabetical order of the grouping value, handy when you are looking for a specific value in the list.
Frequently asked questions
How is this different from the column statistics summary?
Group and count splits the rows into groups by the value of one column and counts (or sums) within each. The statistics summary does not group: it gives one row of measures per column of the file. One answers "how much per category", the other "what each column looks like".
What happens to rows with no value in the grouping column?
They are not lost. They gather under (vacío) and are counted separately, so the
sum of all groups is still the total number of rows in your sheet.
Can I group by several columns at once?
Grouping is by a single column. If you need to cross two — country and year, say — combine them first into one with split and merge columns and group by the resulting column.
Does it distinguish case and spaces when grouping?
Values are grouped exactly as they appear. If Chile and chile come out as
two groups, clean the column first with
clean data and group again.
Does the average ignore empty cells?
Yes. The measure only considers the numeric cells in the group, so empty ones don't drag the average down or count as zero.