# How to count values that appear only once in Excel

Count the rows whose value has a COUNTIF of exactly 1: `=SUMPRODUCT(--(COUNTIF(A2:A12,A2:A12)=1))`.

**Excel:** `=SUMPRODUCT(--(COUNTIF(A2:A12,A2:A12)=1))` (Excel 2021, 2024 and Microsoft 365)

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

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| SKU sold |  | Sold only once |
| P-101 |  |  |
| P-104 |  |  |
| P-102 |  |  |
| P-104 |  |  |
| P-107 |  |  |
| P-101 |  |  |
| P-103 |  |  |
| P-108 |  |  |
| P-104 |  |  |
| P-110 |  |  |
| P-102 |  |  |

Formula in D1: `=SUMPRODUCT(--(COUNTIF(A2:A12,A2:A12)=1))`

Result: 4

## How it works

`COUNTIF(A2:A12,A2:A12)` gives each row the number of times its SKU appears. Rows with a count of 1 hold the SKUs that never repeat, and SUMPRODUCT counts them: P-107, P-103, P-108 and P-110.

This differs from a unique (distinct) count, which would also include one copy of each repeated SKU and return 7.

## Other ways

- `=COUNTA(UNIQUE(A2:A12,,TRUE))`: Excel 365 and Google Sheets: UNIQUE's third argument, TRUE, returns only values that occur exactly once.
- `=COUNTA(UNIQUE(A2:A12))`: The distinct count, for comparison. (Returns 7: every SKU counted once, repeated or not.)

## Common mistakes

- If no value appears exactly once, `UNIQUE(…,,TRUE)` returns an error and COUNTA counts it as 1. The SUMPRODUCT version correctly returns 0.
- Blank cells in the range are treated as values by COUNTIF and can distort the count. Keep the range to the filled rows.

## Questions

**How do I list the values that appear only once?**

Use `=UNIQUE(A2:A12,,TRUE)` in Excel 365 or Google Sheets.

**How do I count values that appear more than once?**

Count distinct values that repeat: `=COUNTA(UNIQUE(FILTER(A2:A12,COUNTIF(A2:A12,A2:A12)>1)))`.

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/count-values-that-appear-only-once