To total values by group, use SUMIF: =SUMIF(B2:B13,E3,C2:C13) — the first range holds the group names, E3 is the group you’re totalling, and the third range holds the values. Point the criteria at a cell rather than typing the group name, so you can drag the formula down a list of groups and get all the totals at once.
To build that list of unique group names in the first place, use =UNIQUE(B2:B13) in Excel 365, or Data → Remove Duplicates on a copy of the column in older versions. For two groups at once — region and gender, say — switch to SUMIFS, where the values range comes first: =SUMIFS(D2:D13,B2:B13,F3,C2:C13,G2).
Two things worth knowing: SUMIF is not case-sensitive, so “North” and “north” total together, and a trailing space in the data means that row is treated as a different group and quietly excluded. If you’re building a summary table rather than a single figure, a pivot table gives you every group’s total in a few clicks.
Let’s say you have a list of groups with the number of people in each group; now, you need to write a formula to perform the sum by the group.
For this, you can use SUMIF or SUMIFS.
Formula to Sum Values by Group
- Enter SUMIF in a cell.
- Refer to the range (B2:B13) where you have the names of groups.
- Specify the group name or refer to the cell where you have the group name.
- Refer to the range (C2:C13) to sum values.
- Close the function and hit Enter to get the result.
The moment you hit Enter, it returns the sum of the North group in the cell.
And you can drag the formula down to all the groups to get the sum. Before you drag it, lock the two ranges with dollar signs ($B$2:$B$13 and $C$2:$C$13), so they don’t move down with the formula; only the group name cell should change.
=SUMIF($B$2:$B$13,E3,$C$2:$C$13)
Sum by Multiple Groups
In the following example, we have two groups with two different columns.
To sum values based on two groups, you need to use SUMIFS instead of SUMIF. In the first criteria, we used Group 1 (Region); in the second, we used Group 2 (Gender).
B2:B13 is the column for Group 1, and C2:C13 is for Group 2.
=SUMIFS($D$2:$D$13,$B$2:$B$13,F3,$C$2:$C$13,G3)
Here, D2:D13 has the values to sum, F3 has the region, and G3 has the gender for that row of the result table. The ranges are locked, so you can copy the formula down for all the combinations.
Get the List of Groups and Sums Automatically
In Excel 365 and Excel 2021, you don’t need to type the group names yourself. UNIQUE returns the list of groups, and SUMIF can use that spilled list to return all the sums in one go. Enter this in E3 to get the group names:
=UNIQUE(B2:B13)
And this in F3 to get the sum for each of them (the # refers to the whole spilled list):
=SUMIF(B2:B13,E3#,C2:C13)
And if you have Microsoft 365, the GROUPBY function returns the groups and their sums in a single formula:
=GROUPBY(B2:B13,C2:C13,SUM)
Sum by Group with a Pivot Table
If you don’t want to use formulas, a pivot table does the same work. Select the data, go to Insert ➜ PivotTable, drag the group column to Rows and the numbers column to Values. For the two-group example, add the second group column to Columns.
Common Mistakes
- Extra spaces: “North ” with a space at the end doesn’t match “North”, so SUMIF returns 0. Clean the names with TRIM.
- Ranges of different sizes: the group range and the sum range should have the same number of rows, like B2:B13 and C2:C13.
- Numbers stored as text: SUMIF skips them, so convert them into numbers first.
Frequently Asked Questions
Is SUMIF case-sensitive?
No. SUMIF treats “north” and “North” as the same group. If you need a case-sensitive sum, use SUMPRODUCT with EXACT.
How do I sum by group with a partial match?
Use wildcards in the criteria. For example, “North*” sums all the groups that start with North, like North-East and North-West:
=SUMIF($B$2:$B$13,"North*",$C$2:$C$13)
How do I count instead of sum by group?
Use COUNTIF with the same group range and group name. It returns the number of rows for each group instead of the total:
=COUNTIF($B$2:$B$13,E3)
Thank you! I have followed several videos to successfully achieve this, but none were straightforward. Your instructions are so straightforward, easy to follow and easy to achieve the desired result in a very quick timeframe. If I had found you sooner, I would have saved ALOT of time. I have bookmarked your webpage to help me quickly with all my excel needs from now on. Thank you for sharing your knowledge!