# How to sum values without counting duplicates in Excel

Divide each amount by how often its ID appears, then add: `=SUMPRODUCT(B2:B10/COUNTIF(A2:A10,A2:A10))`.

**Excel:** `=SUMPRODUCT(B2:B10/COUNTIF(A2:A10,A2:A10))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Invoice | Amount |  | Total, each invoice once |
| INV-1041 | 1250 |  |  |
| INV-1042 | 480 |  |  |
| INV-1041 | 1250 |  |  |
| INV-1043 | 2310 |  |  |
| INV-1044 | 915 |  |  |
| INV-1042 | 480 |  |  |
| INV-1045 | 3600 |  |  |
| INV-1046 | 275 |  |  |
| INV-1043 | 2310 |  |  |

Formula in E1: `=SUMPRODUCT(B2:B10/COUNTIF(A2:A10,A2:A10))`

Result: $8,830

## 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.

## Other ways

- `=SUM(INDEX(UNIQUE(A2:B10),0,2))`: 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!.

## 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))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-without-counting-duplicates