How to count values that appear only once in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--(COUNTIF(A2:A12,A2:A12)=1))Count the rows whose value has a COUNTIF of exactly 1: =SUMPRODUCT(--(COUNTIF(A2:A12,A2:A12)=1)).
- Result on the example
- 4 in
D1 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in D1. Change any input and the result updates, or edit the formula to experiment.
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.
=SUMPRODUCT(--(COUNTIF(A2:A12,A2:A12)=1))Result4
A2:A1211 rows × 1 col: "P-101", "P-104", "P-102", "P-104", …A range: 11 cells in one column.
COUNTIF(A2:A12,A2:A12){11 rows × 1 col} 2, 3, 2, 3, …COUNTIF: Counts cells that meet one condition.- range
A2:A1211 rows × 1 col: "P-101", "P-104", "P-102", "P-104", … - criteria
A2:A1211 rows × 1 col: "P-101", "P-104", "P-102", "P-104", …
- range
COUNTIF(A2:A12,A2:A12)=1{11 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
-(COUNTIF(A2:A12,A2:A12)=1{11 rows × 1 col} 0, 0, 0, 0, …Makes the value negative.
--(COUNTIF(A2:A12,A2:A12)=1{11 rows × 1 col} 0, 0, 0, 0, …Makes the value negative.
SUMPRODUCT(--(COUNTIF(A2:A12,A2:A12)=1))4SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--(COUNTIF(A2:A12,A2:A12)=1{11 rows × 1 col} 0, 0, 0, 0, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--(COUNTIF(A2:A12,A2:A12)=1)). We ran it in Google Sheets mode on the same data and it returns 4.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =COUNTA(UNIQUE(A2:A12,,TRUE)) | 4 | Excel 365 and Google Sheets: UNIQUE's third argument, TRUE, returns only values that occur exactly once. |
| =COUNTA(UNIQUE(A2:A12)) | 7Different result | 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.
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.
Count values that appear only once
D1, count the SKUs in A2:A12 that appear exactly once.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))).
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.