Sum Values by Group

- Written by Puneet

excelchamps-free-courses-puneet-gogia

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.

sum-values-by-group

For this, you can use SUMIF or SWUMIFS.

Formula to Sum Values by Group

  1. Enter SUMIF in a cell.
  2. Refer to the range (B2:B13) where you have the names of groups.
  3. Specify the Group name or refer to the cell where you have the group to name.
  4. Refer to the range (C2:C13) to sum values.
  5. Close the function and hit enter to get the result.
sumif-to-sum-values-by-group

The moment you hit enter, it returns the sum of the North group in the cell.

sum-of-referred-group-values

And you can drag the formula down to all the groups to get the sum.

drag-formula-to-all-the-groups
=SUMIF(B2:B13,E3,C2:C13)

Sum by Multiple Groups

In the following example, we have two groups with two different columns.

sum-by-multiple-groups

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).

sumifs-to-sum-values-of-two-groups

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

Last Updated: March 24, 2024

Leave a Comment