Often you may want to group the values of some dataset in Excel by name.
For example, suppose we have the following dataset and would like to sum the total sales by name of the employee:
The following step-by-step example shows how to do so.
Step 1: Enter the Data
First, enter the data values into Excel:
Step 2: Find the Unique Names
Next, we need to use the =UNIQUE() function to produce a list of unique names in a certain range.
In our example, we’ll type the following formula in cell D2:
=UNIQUE(A2:A12)
This will produce a list of unique names:
Step 3: Calculate the Sum by Name
Next, we will use the SUMIF(range, criterion, sum_range) function to find the sum of the sales by each employee.
We will type the following formula into cell E2:
=SUMIF($A$2:$A$12, D2, $B$2:$B$12)
We can then click and drag this formula down to each remaining cell in column E:
From the output we can see:
- The sum of sales made by Andy is 23
- The sum of sales made by Bob is 12
- The sum of sales made by Chad is 28
- The sum of sales made by Doug is 17
Note that in this example we used the SUMIF function to calculate the sum by name.
However, you could instead use the COUNTIF, AVERAGEIF or some other function to instead calculate the count, average or some other aggregate metric grouped by name.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Sum by Color in Excel
How to Sum by Month in Excel
How to Count by Group in Excel
