Count Excel Without Duplicates: Formulas That Work

To count distinct, nonblank values in Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0), replacing A2:A100 with your data range. The formula returns the number of different entries, counting each value once and returning 0 if the range contains no nonblank values.

What “count without duplicates” means

People use “count without duplicates” to mean two different things: count each different value once, or count only values that appear exactly once. These are not the same calculation. If a list contains Mia, Leo, Mia, the distinct-value count is 2; the count of values appearing exactly once is 1 because Leo appears once and Mia does not.

The formula in the opening paragraph calculates a distinct count. It ignores empty cells, including cells whose formulas return an empty string. Excel’s UNIQUE function treats text comparisons without regard to letter case, so “Mia” and “MIA” are generally treated as the same entry. Spaces and other characters still matter: “Mia” and “Mia ” can be different values.

Before choosing a method, decide what should count as the same. A list of customer names, invoice numbers, and product categories may each need different rules. For example, you might want to count each customer once, count every customer-product pairing once, or count only customers who made exactly one purchase.

Count distinct values with UNIQUE

For Microsoft 365 and Excel 2021 or later, UNIQUE is usually the clearest option. It creates a list of distinct values; ROWS counts the items in that list. FILTER removes blanks before UNIQUE runs, and IFERROR returns 0 if there are no qualifying values. UNIQUE, FILTER, and the other dynamic-array functions in this formula are not available in Excel 2019 and earlier.

For a concrete example, suppose A2:A10 contains Mia, Leo, Mia, a blank cell, Nia, Leo, Omar, a blank cell, and Mia. With that exact layout, enter =IFERROR(ROWS(UNIQUE(FILTER(A2:A10,A2:A10<>""))),0). The result is 4: the distinct nonblank names are Mia, Leo, Nia, and Omar.

If you want to see the distinct values as well as count them, first filter out blank cells and then use UNIQUE. For names in A2:A10, enter =UNIQUE(FILTER(A2:A10,A2:A10<>"")). The result spills into cells below the formula, listing each distinct nonblank name once. Keep the spill area clear; otherwise Excel may show a #SPILL! error.

When blanks should count as one distinct value, remove FILTER and use UNIQUE on the range directly. With data in A2:A100, enter =ROWS(UNIQUE(A2:A100)). This counts a blank as a distinct item if the input includes blanks, so use it only when that behavior matches your goal.

Count unique entries that meet a condition

You can combine FILTER with one or more conditions to count distinct values from only the rows you care about. For example, suppose customer names are in A2:A100 and regions are in B2:B100. To count nonblank customers in the North region, enter =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,(A2:A100<>"")*(B2:B100="North")))),0). The result is the number of different customer names in rows where the region is North.

The asterisk between the two tests means both conditions must be true. For an either-or condition, use addition between the tests instead, taking care to avoid counting a row twice if it meets both. Make sure every condition range has the same dimensions as the values range; mismatched range sizes can cause a calculation error.

For more than one filter condition, add another test inside the parentheses and connect it with an asterisk when all conditions must be met. For example, you could count customers in the North region with a status of Active by also testing a status column. When a condition is based on a date, use actual Excel dates rather than text that only looks like a date, and use cell references for changeable criteria when practical.

Count distinct rows or combinations

Sometimes the unique item is a combination of fields, not a single cell. For example, a sales report may need to count each customer-and-product pairing once. With customer names in A2:A100 and products in B2:B100, use =IFERROR(ROWS(UNIQUE(FILTER(A2:B100,A2:A100<>""))),0) to count distinct two-column rows, excluding rows where the customer cell is blank.

In that example, a customer buying two different products creates two distinct rows, while repeated rows for the same customer and product count once. The filter condition checks only column A. If rows with a blank customer but a populated product should be included, set the inclusion rule to match the data instead—for instance, test whether either field is nonblank. Decide how incomplete rows should be handled before interpreting the result.

For a distinct count based on several columns, provide those columns together as the array passed to UNIQUE. The rows are compared as combinations, so a row is distinct if at least one of its included fields differs. This is not the same as counting unique values in each column separately and adding those counts.

Options for older Excel versions

Excel 2019 and earlier do not have UNIQUE or FILTER. A common formula-based alternative uses SUMPRODUCT and COUNTIF. With values in A2:A100 and no errors in that range, enter =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100)). This counts each nonblank value once and does not require the Ctrl+Shift+Enter array-formula keystroke in Excel for Windows.

COUNTIF comparisons are case-insensitive, which suits many lists but not every task. COUNTIF also treats the asterisk and question mark as wildcard characters in criteria. As a result, this older formula can produce unexpected results when entries contain those characters. It can also be less suitable when the range contains errors. If those cases matter, clean or transform the data first, or use a method that handles the specific values explicitly.

For a list where values may contain wildcard characters, another practical choice is to remove duplicates from a copy of the data and count the resulting entries. This is a manual, destructive-to-the-selected-range operation if performed on the original, so make a copy first. The menu route in Excel for Windows is Data > Remove Duplicates. Excel displays a dialog where you choose which columns define a duplicate; selecting multiple columns makes Excel compare those columns together.

Use a PivotTable for repeatable summaries

A PivotTable can be useful when you need a distinct count as part of a report that will be refreshed as source data changes. In supported Windows desktop versions of Excel, create a PivotTable and select the option to add the data to the Data Model. Add the field you want to count to Values, open Value Field Settings, and choose Distinct Count. The Data Model option is important: a standard PivotTable does not always offer Distinct Count as a summary calculation.

Distinct Count in a Data Model PivotTable is particularly helpful for larger reports or for comparing distinct counts by groups, such as unique customers by region. It does not replace checking the source data: decide how blanks should be treated and whether the field contains consistent values. Data Model and PivotTable options vary by Excel platform; the Windows desktop steps may not be available in the same way in Excel for Mac or Excel for the web.

Use a formula when you want a result directly in the worksheet, a PivotTable when you want a grouped report, and Remove Duplicates when you intend to create a de-duplicated copy of the data. A PivotTable summarizes; it does not clean the original list. Removing duplicates changes the selected data, so preserve a backup when the original records need to remain intact.

Distinct count versus values that appear once

If your actual question is “How many entries occur only one time?”, a distinct count is not the right answer. For a range without errors, a common formula for values that appear exactly once is =SUMPRODUCT((A2:A100<>"")*(COUNTIF(A2:A100,A2:A100)=1)). It excludes blanks and counts an entry only when COUNTIF finds it once in the range.

For example, if A2:A7 contains Mia, Leo, Mia, Nia, Omar, and Omar, the distinct count is 4: Mia, Leo, Nia, and Omar. The count of values appearing exactly once is 2: Leo and Nia. This difference matters in audits, inventory checks, and finding records that have no matching repeat.

Like the older distinct-count formula, this COUNTIF-based formula is case-insensitive and can be affected by wildcard characters in text. If your data has those edge cases, verify a small sample manually or use a controlled cleanup process before relying on the result.

Common problems and how to avoid them

Blanks change the answer. Decide whether an empty cell, a formula returning an empty string, or a cell containing spaces should count. The FILTER test A2:A100<>”” excludes empty cells and empty strings, but a cell containing one or more spaces is not empty.
Extra spaces create apparent duplicates. “North” and “North ” may be treated as different text. Clean inconsistent spacing in a helper column with TRIM if appropriate, then count the cleaned values. TRIM removes ordinary leading and trailing spaces and reduces repeated internal spaces.
Numbers stored as text can differ from numbers. A numeric 123 and text “123” may be treated as different types in a distinct list. Check the source formatting and convert values consistently if they represent the same identifier.
Errors in the source can affect formulas. UNIQUE or FILTER may pass errors through, while COUNTIF-based calculations can also fail or give confusing results. Find and resolve source errors, or deliberately exclude error cells before counting.
A filtered worksheet is not automatically a filtered formula. A UNIQUE/FILTER formula based on a range does not necessarily limit itself to rows hidden by AutoFilter or manually hidden rows. Add an explicit condition to the formula if the distinct count should reflect a particular category or status.
Dynamic results need room to spill. If a formula that returns a list has occupied cells below it, Excel may show #SPILL!. Clear the cells in the spill area or move the formula.

Frequently Asked Questions

How do I count unique names in Excel?
With names in A2:A100 in Microsoft 365 or Excel 2021 and later, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0). It counts each nonblank name once.

Does Excel count blank cells as duplicates?
It depends on the formula. The FILTER version shown here excludes blank cells before counting. UNIQUE applied directly to a range can include a blank as one distinct item. A formula cell displaying an empty string is also excluded by the FILTER test against an empty string.

How do I count unique values in Excel 2019?
Excel 2019 does not include UNIQUE or FILTER. For a simple range without errors or wildcard-character complications, try =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100)). Alternatively, remove duplicates from a copy of the list and count the remaining entries.

How do I count distinct values by category?
Use FILTER to keep rows matching the category, then UNIQUE and ROWS to count the distinct values. For names in A2:A100 and category labels in B2:B100, the North-region formula above excludes blank names and counts only rows marked North.

What is the difference between unique and distinct count?
In everyday Excel questions, “unique count” often means a distinct count: each different value counts once, even if repeated. Strictly, “values that occur exactly once” means entries with no repeats. Check which meaning your task requires before choosing a formula.

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.