How to Sum Unique Values in Excel With UNIQUE

To sum unique values in Excel, wrap UNIQUE inside SUM: =SUM(UNIQUE(A2:A9)). UNIQUE removes the repeats and SUM adds what's left. But "unique" can mean three different things: each different value once, only the values that never repeat, or each record once. This guide shows the right formula for each, plus a version for older Excel. It's for anyone cleaning up duplicate exports or answering a "total without duplicates" question in a job test.

Which "unique" do you mean?#

Before you write the formula, decide which total you need. Here is a list of fees in A2:A9:

Distinct values are 40, 25, 15, 60 and 10, which add up to 150. Values that appear exactly once are 15, 60 and 10, which add up to 85. Both are "unique", so check which one the question means.

How to sum distinct values with SUM and UNIQUE#

UNIQUE returns each different value once, and SUM adds them. In an empty cell, type:

=SUM(UNIQUE(A2:A9))

The result is 150. On its own, =UNIQUE(A2:A9) would spill the five distinct values down the column. Inside SUM, nothing spills: SUM adds the list in memory and returns one number.

UNIQUE needs Excel 2021, Excel 2024 or Microsoft 365. In older versions it shows #NAME?; use the SUMPRODUCT method further down. The UNIQUE function guide and the SUM function guide cover both functions in detail.

How to sum values that appear only once#

UNIQUE has a third argument, exactly_once. Set it to TRUE and UNIQUE keeps only values that occur a single time:

=SUM(UNIQUE(A2:A9,,TRUE))

The two commas skip the second argument (by_col), which you only need for data laid out across columns. The result is 85: 40 and 25 repeat, so they are left out completely.

How to sum each record once, not each amount#

Real duplicates are usually whole rows, such as an invoice exported twice. Here, deduping the amounts alone gives the wrong answer:

=SUM(UNIQUE(B2:B7)) returns 800, because INV-102 and INV-103 both have an amount of 400, and UNIQUE keeps only one 400. That loses a real invoice.

Instead, run UNIQUE on the ID and amount together, then add the amount column:

=SUM(INDEX(UNIQUE(A2:B7),,2))

1. UNIQUE(A2:B7) keeps each row once: four invoices.
2. INDEX(...,,2) takes column 2 of that result, the amounts. Leaving the row number empty means "all rows".
3. SUM adds them: 250 + 400 + 400 + 150 = 1,200.

In Microsoft 365, CHOOSECOLS(UNIQUE(A2:B7),2) does the same as the INDEX part. If one invoice ID appears with two different amounts, UNIQUE keeps both rows, so check those by hand. To dedupe across several columns, see the guide to UNIQUE across multiple columns.

How to sum unique values in older Excel#

Without UNIQUE, use SUMPRODUCT with COUNTIF:

=SUMPRODUCT(A2:A9/COUNTIF(A2:A9,A2:A9))

COUNTIF(A2:A9,A2:A9) returns how many times each value appears: 3 for each 40, 2 for each 25, and so on. Dividing each value by its count splits it into equal shares. The three 40s each become 40 ÷ 3, which add back up to one 40. The result is 150, the same as SUM of UNIQUE.

For the invoice table, count the IDs instead of the amounts:

=SUMPRODUCT(B2:B7/COUNTIF(A2:A7,A2:A7))

That also returns 1,200. If the range has blank cells, COUNTIF counts them as 0 and the formula shows #DIV/0!. This version skips blanks: =SUMPRODUCT((A2:A9<>"")*A2:A9/COUNTIF(A2:A9,A2:A9&"")).

How to total by each unique item#

Sometimes "sum unique" means a total per item: sales per region, hours per person. Combine UNIQUE with SUMIF. If A2:A20 holds regions and B2:B20 holds sales:

1. In D2, type =UNIQUE(A2:A20) to spill the list of regions.
2. In E2, type =SUMIF(A2:A20,D2#,B2:B20).

The # after D2 means "the whole spilled list", so E2 spills one total per region and grows when a new region appears. To sort the list too, see how to make a sorted unique list. A pivot table gives the same totals, but it adds every row, so remove duplicate rows first.

Sum unique values with a condition#

Add FILTER to keep only the rows you want before UNIQUE removes duplicates. To total each paid invoice once, with the status in C2:C7:

=SUM(INDEX(UNIQUE(FILTER(A2:B7,C2:C7="Paid")),,2))

FILTER keeps the paid rows, UNIQUE drops repeated invoices, and SUM adds the amounts. The guide to FILTER with SUM explains the FILTER part, and counting unique values with criteria uses the same pattern with COUNTA. If no rows match, FILTER returns #CALC!; add a third argument such as 0 to avoid it.

Common problems#

• Numbers stored as text. UNIQUE treats the number 40 and the text "40" as different values, and SUM ignores the text one. Convert the column to numbers first.
• Extra spaces. "INV-101 " with a trailing space is a different ID from "INV-101". Clean IDs with TRIM.
• Wrong kind of unique. Check whether you need distinct values, once-only values or unique records. The results differ.
• #NAME? in older Excel. UNIQUE isn't available before Excel 2021. Use SUMPRODUCT and COUNTIF instead.

Practise it#

Summing unique values is two skills joined: getting the distinct list and adding it up. Practise UNIQUE in a live spreadsheet to list and count distinct values, then practise SUM and AutoSum to total ranges quickly. Each lesson checks your formula as you type. Then try the exercise on this page to total the duplicated invoices only once.