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 who have the group; now, you need to write a formula to perform the sum by the group.
For this, you can use SUMIF or SWUMIFS.
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 to 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.
=SUMIF(B2:B13,E3,C2:C13)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(D2:D13,B2:B13,F3,C2:C13,G2)Get the Excel File
Related Formulas
- Sum Greater than Values using SUMIF
- Sum Not Equal Values (SUMIFS) in Excel
- SUMIF / SUMIFS with an OR Logic in Excel
- SUMIF with Wildcard Characters in Excel
- SUMIFS Date Range (Sum Values Between Two Dates Array)
- Combine VLOOKUP with SUMIF
- Sum IF Cell Contains a Specific Text (SUMIF Partial Text)
- Sum Values Based on Year (SUMIF Year)
- SUMIF By Date (Sum Values Based on a Date)
-
Back to the List of Excel Formulas
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!