Excel COUNTIF Exclude Duplicates: Formula & Steps

Formulas & functions · Updated November 22, 2026

To count unique values in Excel while excluding duplicates, use =SUMPRODUCT(1/COUNTIF(range, range)) (for numbers and text) or =COUNTA(UNIQUE(range)) in Excel 365/2021. The first formula counts each distinct item once; the second returns a dynamic array of unique values and counts them.

Why COUNTIF Alone Counts Duplicates

The standard COUNTIF function counts every cell that meets a condition, including repeated values. For example, if column A contains Apple, Apple, Banana, Cherry, =COUNTIF(A:A,"Apple") returns 2, not 1. This is by design—COUNTIF is a conditional counter, not a unique counter. To exclude duplicates, you need a formula that divides each count by itself, effectively turning each duplicate group into a single 1.

The Classic Formula: SUMPRODUCT(1/COUNTIF)

Assume your data is in cells A2:A10 (adjust the range to fit your data). Enter this formula in any empty cell:

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

How it works: COUNTIF returns an array of counts for each cell—e.g., for Apple, Apple, Banana, it returns {2;2;1}. Dividing 1 by each count gives {0.5;0.5;1}. Summing those fractions yields 2, which is the number of unique items. This formula works for text, numbers, and mixed data, but it can be slow on very large datasets (over ~10,000 rows) because it performs a full range calculation for each cell.

Modern Alternative: UNIQUE Function (Excel 365/2021)

If you have Excel for Microsoft 365, Excel 2021, or Excel for the web, the UNIQUE function is simpler and faster. To count unique values in A2:A10, use:

=COUNTA(UNIQUE(A2:A10))

This returns the number of distinct items. If your range contains blank cells, UNIQUE includes a blank as a unique value, so you may want to filter them out: =COUNTA(UNIQUE(FILTER(A2:A10, A2:A10<>""))). On Mac, the formula is identical; the function is available in Excel 2021 and later.

Counting Unique Values with Conditions

Sometimes you need to count unique values that meet a criterion, such as unique products sold by a specific salesperson. Use the SUMPRODUCT approach with a condition. For example, with salespersons in A2:A20 and products in B2:B20, to count unique products sold by “John”:

=SUMPRODUCT((A2:A20="John") / COUNTIFS(B2:B20, B2:B20, A2:A20, A2:A20))

This divides 1 by the count of each product within John’s rows, then sums. A simpler method in Excel 365: =COUNTA(UNIQUE(FILTER(B2:B20, A2:A20="John"))). The latter is easier to read and maintain.

Handling Blank Cells and Errors

The SUMPRODUCT(1/COUNTIF) formula returns a #DIV/0! error if the range contains any blank cells, because COUNTIF counts blanks as 0. To avoid this, use an array formula that excludes blanks:

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

This only evaluates non-blank cells. Alternatively, use UNIQUE with FILTER as shown above. If your data contains errors (like #N/A), COUNTIF will ignore them, but UNIQUE will include them; you can wrap with IFERROR if needed.

Comparing Methods: Which One Should You Use?

  • SUMPRODUCT(1/COUNTIF): Works in all Excel versions, but slower on large data and fails with blanks.
  • COUNTA(UNIQUE()): Fast, dynamic, and easy to read, but requires Excel 365/2021 or Excel for the web.
  • PivotTable: A non-formula option—drag the field to Rows and use “Count Distinct” (available in Excel 2013+ as a data model feature) or just count the row labels.
  • Remove Duplicates: A one-time data cleanup, not a formula. Use Data > Remove Duplicates, then count the remaining rows.

For most users, COUNTA(UNIQUE()) is the best choice if available. If you need compatibility with older Excel, stick with SUMPRODUCT.

Frequently Asked Questions

How do I count unique values in Excel with multiple criteria?

Use SUMPRODUCT with COUNTIFS, as shown above, or in Excel 365 use COUNTA(UNIQUE(FILTER(range, (criteria1)*(criteria2)))). For example, to count unique products sold by John in the East region: =COUNTA(UNIQUE(FILTER(B2:B20, (A2:A20="John")*(C2:C20="East")))).

Why does my COUNTIF formula count duplicates?

Because COUNTIF counts every cell that matches the criteria, not distinct values. It has no built-in unique mode. To exclude duplicates, you must combine it with other functions or use UNIQUE.

Can I use COUNTIF with a helper column to exclude duplicates?

Yes. Add a helper column with a formula like =IF(COUNTIF($A$2:A2, A2)=1, 1, 0) to mark the first occurrence of each value, then sum that column. This is a common workaround in older Excel versions.

Does the UNIQUE function work in Excel 2019?

No. UNIQUE was introduced in Excel 365 and Excel 2021. In Excel 2019, you must use SUMPRODUCT or a helper column.

How do I count unique values in a pivot table?

In Excel 2013 and later, create a pivot table, add the field to Rows, then add the same field to Values and change the value field settings to “Distinct Count” (requires adding the data to the Data Model). Alternatively, just count the number of row labels manually.

Common Mistakes and Troubleshooting

One common mistake is using =COUNTIF(A:A, A:A) expecting a unique count—it returns the count for the first cell only. Another is forgetting to press Ctrl+Shift+Enter for array formulas in older Excel versions; if you use SUMPRODUCT, it handles arrays natively, but if you use {=SUM(1/COUNTIF(...))}, you must confirm with Ctrl+Shift+Enter. Also, be aware that COUNTIF is not case-sensitive, so “apple” and “Apple” are treated as the same value. If case matters, you’d need a more complex array formula with EXACT.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.