﻿ Excel formula to get the sum of unique values

Excel formula to get the sum of unique values

Let us assume, I have a list of items (or products) and these items are sold in different zones (east, west etc.). I want to get the SUM of unique items and display it. See the below image to understand what I mean.

The first column "Product" has a list of items (repeated few times) followed by number of units (or pieces) sold in the second column.

Column "E" shows a list of unique items (extracted from column "A") followed by the SUM of units sold for each item in column "F".

There are many different ways you can achive this. However, I did this using two different formulas.

Get distinct or unique items from the list

First, lets get the unique items from the list.

The column "Product" has a list of items repeated few times. So, first I'll get the "distinct items from the list" in the "E" column.

Copy the below formula in "E2" column and press Ctrl+Shift+Enter" keys.

Note: Ctrl+Shift+Enter command convert the data into an array format in Excel. The formula (if there are no errors) will be encapsulated inside curly braces { }.

=IFERROR(INDEX(\$A\$2:\$A\$10, MATCH(0,COUNTIF(\$E\$1:E1, \$A\$2:\$A\$10), 0)),"")

Set focus on E2 column and drag it down.

Note: If you have access to Excel for Microsoft 365, Excel 2021 or Excel for the Web, you can use the UNIQUE() function to get unique items from the list.

Get the SUM of distinct items

Once we have extracted unique items from the Product column, now we'll get the SUM of each item.

To get the sum, I am using two very popular Excel built-in functions called SUMIFS() and VLOOKUP().

Copy the below formula in "F2" column and press Ctrl+Shift+Enter keys.

=SUMIFS(B2:B10,A2:A10,VLOOKUP(E2:E8,A2:B10,1,FALSE))

Set focus on "F2" column and drag it down.

Now, whenever you make any changes in the 1st or 2nd columns, the changes will reflect in columns "E" and "F".

🙂