Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to sum unique values in Excel and Google Sheets
Topic:SUM & aggregationExcel & Google Sheets
=SUMPRODUCT(B2:B6/COUNTIF(B2:B6,B2:B6))Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Place the numeric values in column B (B2:B6). Duplicate values represent entries you want counted only once.
- 2Enter =SUMPRODUCT(B2:B6/COUNTIF(B2:B6,B2:B6)). For each row, B divides by the frequency of that value in the range, so duplicates share the contribution.
- 3Press Enter. The result equals the sum of each unique value taken once.
Tips
- This formula divides by COUNTIF, so a zero in the range will cause a #DIV/0! error. Wrap with IFERROR if zeros are possible: =SUMPRODUCT(IFERROR(B2:B6/COUNTIF(B2:B6,B2:B6),0)).
- If you want to sum unique values in a category (e.g. distinct amounts per region), combine with an IF: =SUMPRODUCT((A2:A6="East")*B2:B6/COUNTIFS(A2:A6,A2:A6,B2:B6,B2:B6)).
- In Excel 365 / Sheets with UNIQUE available: =SUM(UNIQUE(B2:B6)) is simpler and more readable.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
What exactly does 'sum unique values' mean — summing deduplicated amounts or summing amounts that appear only once?
This tutorial sums each distinct value once (deduplicated sum). If you want only values that appear exactly once, filter with COUNTIF=1: =SUMPRODUCT((COUNTIF(B2:B6,B2:B6)=1)*B2:B6).
Can I do this with text labels instead of numbers?
Counting unique text labels is done with SUMPRODUCT(1/COUNTIF(range,range)) which returns the count of distinct values, not a sum of numbers.
More on SUM & aggregation
See all →Reference
SUMPRODUCT
Multiply ranges together row by row and sum the result — ideal for weighted totals.
How-toHow to sum a column
Add up an entire column with SUM. Total a whole column or a fixed range — identical in Excel and Google Sheets.
Error fixFix 0
SUM returns 0 or the wrong total in Excel and Google Sheets — fix numbers stored as text, filtered ranges, manual calculation mode, and circular references.
ReferenceSUBTOTAL
Aggregate a range (sum, average, count…) while ignoring other SUBTOTALs and, optionally, hidden rows. Ideal under filters.
How-toHow to create a running total
Build a cumulative running total with a SUM and a clever anchored reference that grows as you copy it down. Works in both apps.
How-toHow to sum values by month
Total amounts that fall in a given month with SUMIFS and two date bounds. Works the same in Excel and Google Sheets.