Count unique values among duplicates

To count unique values among duplicates in Excel, you have a few methods depending on your version of Excel.

✅ 1. Excel 365 / 2019+: Use UNIQUE() + COUNTA()

If your data is in column A (A2:A100):

=COUNTA(UNIQUE(A2:A100))

🟢 This counts how many distinct values are in that range — duplicates are ignored.

✅ 2. Excel 2016 or earlier: Use SUMPRODUCT + COUNTIF

If you don’t have the UNIQUE function:

=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))

This counts each distinct value once, even if it appears multiple times.

⚠️ Note: This array-style formula may return errors if there are blanks. Use this safer version:

=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100, A2:A100 & ""))

✅ 3. Count Unique Based on a Condition

Example: Column A = Names, Column B = Region
Count unique names only where Region = “North”

=SUM(--(FREQUENCY(IF(B2:B100="North", MATCH(A2:A100, A2:A100, 0)), ROW(A2:A100)-ROW(A2)+1)>0))

🔒 Press Ctrl+Shift+Enter in older Excel (not needed in Excel 365).

✅ 4. Using Pivot Table (No Formula)

1. Select your data.
2. Go to Insert > PivotTable.
3. Drag the column with duplicates into the Rows area.
4. Any field into the Values area (or use “Count”).
5. You’ll now see the list of unique items in the Rows area.