How to sum values without counting duplicates in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(B2:B10/COUNTIF(A2:A10,A2:A10))Divide each amount by how often its ID appears, then add: =SUMPRODUCT(B2:B10/COUNTIF(A2:A10,A2:A10)).
- Result on the example
- $8,830 in
E1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.
How it works
COUNTIF(A2:A10,A2:A10) returns, for each row, how many rows share its invoice number. INV-1041 appears twice, so each of its rows contributes half of 1,250, and the pair adds up to one 1,250. Every invoice ends up counted exactly once.
=SUMPRODUCT(B2:B10/COUNTIF(A2:A10,A2:A10))Result8830
B2:B109 rows × 1 col: $1,250, $480, $1,250, $2,310, …A range: 9 cells in one column.
A2:A109 rows × 1 col: "INV-1041", "INV-1042", "INV-1041", "INV-1043", …A range: 9 cells in one column.
COUNTIF(A2:A10,A2:A10){9 rows × 1 col} 2, 2, 2, 2, …COUNTIF: Counts cells that meet one condition.- range
A2:A109 rows × 1 col: "INV-1041", "INV-1042", "INV-1041", "INV-1043", … - criteria
A2:A109 rows × 1 col: "INV-1041", "INV-1042", "INV-1041", "INV-1043", …
- range
B2:B10/COUNTIF(A2:A10,A2:A10){9 rows × 1 col} 625, 240, 625, 1155, …The / operator divides.
SUMPRODUCT(B2:B10/COUNTIF(A2:A10,A2:A10))8830SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
B2:B10/COUNTIF(A2:A10,A2:A10){9 rows × 1 col} 625, 240, 625, 1155, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(B2:B10/COUNTIF(A2:A10,A2:A10)). We ran it in Google Sheets mode on the same data and it returns $8,830.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(INDEX(UNIQUE(A2:B10),0,2)) | $8,830 | Excel 365 and Google Sheets: UNIQUE removes duplicate rows, INDEX takes the amount column and SUM adds it. |
Common mistakes
=SUM(UNIQUE(B2:B10))removes duplicate amounts, not duplicate invoices. Two different invoices that both happen to be 480 would collapse into one.- The division method assumes duplicate rows have the same amount. If they differ, it averages them; decide which row is right first.
- A blank in the ID column makes COUNTIF return 0 for that row and the formula returns #DIV/0!.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Sum values without counting duplicates
E1, total the amounts so that each invoice number counts only once.Questions
How do I find the duplicates first?
Flag them with =COUNTIF($A$2:$A$10,A2)>1 filled down.
How do I sum distinct numbers?
If you really mean each different value once, use =SUM(UNIQUE(B2:B10)).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.