To add each different numeric value once in Excel, put the values in B2:B8 and enter =SUM(UNIQUE(B2:B8)). The formula returns the sum of the distinct numbers, not the sum of every row; the right approach depends on what you mean by “unique.”
What “sum unique values” can mean
In spreadsheet questions, “unique” is used for two related but different ideas. Most often, someone wants to add each distinct value once, even if it occurs several times. If a list contains 12, 12, 18, 18, and 25, that total is 55: 12 + 18 + 25. The repeated copies do not add to the result.
Another possible meaning is to add only values that appear exactly once. With the same list, that result is 25, because 12 and 18 each occur more than once. Excel’s UNIQUE function can do either task, but its optional exactly-once setting changes the question being answered.
There is also a common variation: add one amount for each distinct person, product, or transaction ID. That is not the same as adding distinct amounts. If two different customers each have a sale of 50, a distinct-amount total counts 50 once; a one-sale-per-customer total counts both customers’ sales. Choose the interpretation before building a formula.
Use UNIQUE to sum each distinct number once
For Microsoft 365, Excel 2021, or Excel 2024, the basic formula is short. With numeric values in B2:B8, enter =SUM(UNIQUE(B2:B8)). UNIQUE creates a dynamic array containing one copy of each distinct value, and SUM adds the numbers in that array.
For example, if B2:B8 contains 12, 12, 18, 18, 25, 25, and 40, UNIQUE returns 12, 18, 25, and 40. SUM therefore returns 95. The source cells are not changed: the formula produces a calculation, rather than deleting or editing duplicate rows.
UNIQUE returns a spilled array when used by itself, meaning Excel displays its results in cells below the formula if they are available. When it is nested inside SUM, the intermediate list is calculated for the formula and does not need its own visible worksheet area. If you want to inspect the distinct list, enter UNIQUE in an empty cell first, then place a separate SUM formula beside or below it.
UNIQUE has optional arguments. Its third argument, exactly_once, asks Excel to return only items that occur one time. With values in B2:B8, this formula sums only values appearing once: =SUM(UNIQUE(B2:B8,,TRUE)). For the example list above, the result is 40, not 95, because 40 is the only number with a single occurrence. The blank second argument keeps the default row-by-row comparison.
Sum distinct values only when a condition matches
A report may need the distinct total for one category, salesperson, or month. Suppose categories are in A2:A10, amounts are in B2:B10, and the category to summarize is in E2. This formula filters the amounts to the selected category, removes repeated amounts, and sums what remains: =IFERROR(SUM(UNIQUE(FILTER(B2:B10,A2:A10=E2))),0).
FILTER keeps rows whose category matches E2. UNIQUE then keeps one copy of each remaining amount, and SUM adds those distinct amounts. IFERROR returns zero if the filter finds no matching rows, rather than showing Excel’s no-results error. Change E2 to calculate a different category. This example treats repeated amounts as duplicates even if they belong to different records; it does not count one sale per distinct customer.
To sum one amount per unique ID instead, assume IDs are in A2:A10 and their amounts are in B2:B10. The formula below adds the amount from the first row Excel finds for each ID: =SUM(XLOOKUP(UNIQUE(A2:A10),A2:A10,B2:B10)). For instance, if an ID appears on three rows, only the amount from its first matching row is included. This is appropriate only when first-row selection is the intended rule, or when repeated rows for an ID carry the same amount. If amounts differ and you need the latest, largest, or otherwise preferred record, define that rule explicitly before summing.
Distinct values versus values appearing exactly once
This distinction is the most frequent source of surprising results. “Distinct values” means one representative of every different value. “Values appearing exactly once” excludes anything that has a duplicate. In the list 12, 12, 18, 18, 25, 25, 40, the distinct-value total is 95, while the exactly-once total is 40.
In modern Excel, the default =SUM(UNIQUE(B2:B8)) calculates the first interpretation. The exactly-once version, with values in B2:B8, is =SUM(UNIQUE(B2:B8,,TRUE)). Do not add TRUE simply because the task uses the word “unique”; check whether repeated values should be counted once or left out altogether.
Another useful check is to write out the expected list before summing. If your intended list includes one 12, one 18, and one 25, use the default behavior. If it should include only numbers that occur a single time in the source, use exactly_once. This small clarification can prevent a formula that is syntactically correct but answers the wrong business question.
What happens with text, blanks, and errors
SUM adds numeric values and ignores text and blank cells. That means the basic formula is usually suitable for a column that contains numbers, blank cells, and occasional text labels. However, text that looks like a number may be stored as text, so it may not be included as a numeric amount. Check the cell format and convert text-formatted numbers if the total seems too low.
UNIQUE compares the values it receives, so repeated text entries are reduced to one entry too. But SUM will not add text labels such as “Pending” or “N/A.” A cell containing an Excel error can cause the overall calculation to return an error, so inspect the source range if you see #VALUE! or another error unexpectedly. Avoid wrapping every formula in IFERROR without understanding the cause: doing so can hide bad source data as well as harmless no-match cases.
UNIQUE comparisons are not case-sensitive for ordinary text matching: entries that differ only by capitalization are generally treated as the same value. If capitalization must distinguish items, you need a different, case-sensitive method. Also make sure the range covers the intended records. A fixed range such as B2:B8 will not automatically include new entries typed in B9; an Excel Table can make growing data easier to manage.
Options in older Excel versions
UNIQUE is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. If a workbook is opened in an older release that does not support dynamic arrays, UNIQUE may return a #NAME? error. One alternative is to remove duplicates from a copy of the data and sum the resulting list.
- Copy the source values to a spare column or duplicate the worksheet, so the original records remain intact.
- Select the copied values, then choose Data > Remove Duplicates. In the dialog, confirm the column to check and select OK.
- Sum the remaining numeric values. Removing duplicates changes the selected copy, so do not apply it directly to records that must be preserved.
Another older-Excel option is a legacy array formula. With numbers in B2:B10, this formula sums each distinct numeric value once: =SUM(IF(FREQUENCY(IF(ISNUMBER(B2:B10),MATCH(B2:B10,B2:B10,0)),ROW(B2:B10)-ROW(B2)+1)>0,B2:B10)). In versions that require legacy array entry, confirm it with Ctrl+Shift+Enter on Windows or Control+Shift+Enter on Mac, rather than Enter alone. Those versions display braces around the formula in the formula bar; do not type the braces yourself. This formula is less readable and is intended for numeric values, so the modern UNIQUE formula or a duplicate-removal workflow is often easier to maintain.
Check the result and avoid common mistakes
Before relying on a total, check that the formula’s range and definition match the data. A quick manual check on a short sample is often enough: list each distinct number once, add that list, and compare it with the formula result. For a larger dataset, put =UNIQUE(B2:B10) in an empty area to inspect what Excel considers distinct, then compare that list with the source.
- Check the range: Include every intended row, but avoid unrelated totals or notes below the data.
- Check number storage: A number stored as text may be ignored by SUM even though it looks numeric on screen.
- Check the task: Decide whether duplicates count once, count only when they occur once, or should be grouped by an ID.
- Check for errors: Correct source-cell errors rather than masking them if they affect the calculation.
- Check updates: If new rows are added often, use an Excel Table or update the formula’s range.
For a one-time cleanup, Remove Duplicates is a straightforward option, but it alters the selected data. A formula is better when the source should stay intact and the answer should recalculate as values change. A PivotTable can also summarize records, but its settings and layout need to reflect whether the goal is a distinct amount total or one record per ID; a standard sum of a value field will ordinarily include every row.
Frequently Asked Questions
How do I sum unique values in Excel?
With the numbers in B2:B100, use =SUM(UNIQUE(B2:B100)) in Microsoft 365, Excel 2021, or Excel 2024. It adds one copy of each distinct value. For older Excel, use Remove Duplicates on a copy of the list or a compatible legacy array formula.
How do I sum values that appear only once?
With values in B2:B100, use =SUM(UNIQUE(B2:B100,,TRUE)). The TRUE argument tells UNIQUE to return only entries that occur exactly once. This gives a different result from summing one copy of every distinct value.
Does SUM(UNIQUE()) ignore blank cells?
SUM ignores blanks and text, so blank cells in a numeric source range do not add to the total. If the range includes errors, the formula may return an error instead. Text-formatted numbers may also be excluded, so verify their stored values when totals do not match expectations.
Can I sum unique values based on another column?
Yes. Use FILTER to keep the rows that match a condition, then apply UNIQUE and SUM to the filtered values. For a category in A2:A10, amounts in B2:B10, and the chosen category in E2, use =IFERROR(SUM(UNIQUE(FILTER(B2:B10,A2:A10=E2))),0).
Why does UNIQUE return #NAME?
The Excel version may not support UNIQUE, or the function name may have been entered incorrectly. UNIQUE is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. In an older version, use a duplicate-removal workflow or a legacy-compatible formula.
Does UNIQUE change or delete my original data?
No. UNIQUE returns a calculated list and leaves the source cells unchanged. Data > Remove Duplicates is different: it removes repeated entries from the selected range, so work on a copy when you need to preserve the original list.
Related
- Excel Sum Above: How to Add Cells Above Your Total
- Excel Max Rows per Sheet: Limits and Workarounds
- Excel Max Value in a Column: Find the Largest Number
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Find a Weighted Average in Excel with SUMPRODUCTTo find a weighted average in Excel, multiply each value by its weight, add those products, then divide…
- Add a Subtotal Row to an Excel Table the Right WayTo add a total row to an Excel table in Microsoft 365 for Windows, click anywhere in the…
- How to Merge Names in Excel: Formulas and Flash FillTo combine first and last names in Excel, put the first names in column A and last names…
- Excel Query Function: What to Use InsteadExcel does not have a worksheet function named QUERY like Google Sheets does. In Microsoft 365 for Windows,…
- Excel SORT Function Examples: Data, Names, and DatesExcel’s SORT function creates a sorted copy of a range with a formula: for example, with names in…
- Excel Time Between Two Timestamps: Formulas and TipsIf the start timestamp is in A2 and the end timestamp is in B2, enter =B2-A2 to calculate…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.