COUNTIF Distinct Values in Excel: Count Unique Items

Formulas & functions · Updated November 22, 2026

To count distinct nonblank values in Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0), assuming the values are in A2:A100. If you specifically need a COUNTIF-based formula, use =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&"")); it counts each nonblank value once, even when it appears repeatedly.

These formulas answer slightly different practical needs: UNIQUE is the clearest current option, while COUNTIF can help in older workbooks or when you want to mark first occurrences. The right choice depends on your Excel version, whether blanks should count, and whether you need a total, a list, or a count within a category.

What “distinct” means in Excel

A distinct count is the number of different values in a range, with repeated occurrences counted only once. If A2:A8 contains Oak, Pine, Oak, a blank, 12, 12, and Elm, there are four distinct nonblank values: Oak, Pine, 12, and Elm. The answer is not the total number of filled cells, which is six, or the number of values that appear only once, which is two.

That last distinction matters because Excel has a separate “exactly once” idea. A value that occurs twice still belongs in a distinct count, but it does not qualify as occurring exactly once. In ordinary language, people sometimes call both tasks “count unique values,” so decide which meaning you want before choosing a formula.

The simplest method with UNIQUE

In Microsoft 365, UNIQUE returns a spill range of distinct entries. To count the nonblank values in A2:A100, use the formula shown in the opening answer. FILTER removes blank cells first, UNIQUE removes duplicate values, and ROWS counts the resulting entries. IFERROR returns zero if there are no nonblank values to count.

This method is usually the easiest to read and maintain. It also separates the work into understandable steps, making it simpler to troubleshoot than a legacy all-in-one formula. If you want to see the distinct values rather than just count them, and the source is A2:A100, enter =UNIQUE(FILTER(A2:A100,A2:A100<>"")) in an empty cell. Excel spills the list into the cells below; leave that spill area clear.

UNIQUE, FILTER, and the dynamic-array behavior used here are available in Microsoft 365 and Excel 2021 or later. Excel for the web also supports modern dynamic-array formulas. If you open a workbook in an older Excel release that does not support these functions, the formula may display as an unsupported-function error rather than calculate.

A COUNTIF formula for a distinct count

For a COUNTIF-based total, assume the source values are in A2:A100 and use =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&"")). COUNTIF calculates how many times each value occurs in the full range. Dividing one by that frequency gives each occurrence a fraction: a value appearing four times contributes four shares of one quarter, which add to one. SUMPRODUCT adds those contributions, while the condition before the division excludes blank cells.

For example, with Oak, Pine, Oak, blank, 12, 12, and Elm in A2:A8, the frequencies for the nonblank entries are two, one, two, two, and one. The contributions add up to 1 + 1 + 1 + 1 = 4, so the formula returns four distinct nonblank values. The formula is useful in many traditional workbooks, but it is less transparent than the UNIQUE approach and has important limitations.

COUNTIF is not case-sensitive, so Oak and OAK are treated as the same value. Its criteria also interpret wildcard characters: an asterisk (*) can match any sequence of characters, and a question mark (?) can match one character. If your data contains literal wildcard characters, or if case differences must count as different values, do not assume this COUNTIF formula will give the result you intend. Clean or transform the data, or use a method designed for those rules.

Count first occurrences with a helper column

A helper column is useful when you want to see which rows contribute to the count, or when colleagues need a formula they can inspect row by row. Assume values are in A2:A100. In B2, enter =IF(A2="","",--(COUNTIF($A$2:A2,A2)=1)), then fill it down through B100. The expanding range starts at A2 and ends at the current row, so COUNTIF checks how often that value has appeared so far. The first occurrence returns 1; later occurrences return 0; blank source cells remain blank.

To total the helper flags, with those results in B2:B100, enter =SUM(B2:B100). For the sample list Oak, Pine, Oak, blank, 12, 12, and Elm, the helper flags are 1, 1, 0, blank, 1, 0, and 1. Their sum is four. This approach makes duplicate handling visible, which can be helpful for auditing or filtering, but it adds a column and still inherits COUNTIF’s case and wildcard behavior.

When using the helper formula, enter it in B2 and copy it down with the fill handle, or select the destination cells and use Ctrl+D on Windows after placing the formula in the top cell. On Mac, the equivalent fill-down shortcut is Control+D. The formula can also be placed in an Excel Table column; structured table references can make formulas expand as new rows are added, though the exact reference formula depends on the table and column names.

Count distinct values within a category

Often the real question is not “How many distinct values are in this whole column?” but “How many distinct items belong to this department, customer, or month?” Assume categories are in A2:A100, item names are in B2:B100, and the category to examine is in E2. To count nonblank distinct items for that category, use =IFERROR(ROWS(UNIQUE(FILTER(B2:B100,(A2:A100=E2)*(B2:B100<>"")))),0).

FILTER keeps rows where the category equals E2 and the item is not blank. The multiplication acts as an AND test because both conditions must be true. UNIQUE removes repeated items, and ROWS counts the remaining results. If E2 contains “North,” for example, and the matching rows contain Bolt, Nut, Bolt, and Washer, the result is three. If there are no matching nonblank items, IFERROR displays zero instead of FILTER’s no-results error.

For a breakdown across many categories, copy the formula alongside a list of category names, changing E2 to the appropriate cell on each row. For a larger recurring report, a PivotTable may be more convenient. In Excel for Microsoft 365 on Windows, create a PivotTable using Insert > PivotTable and select Add this data to the Data Model in the dialog. Put the category in Rows and the item field in Values, then use Value Field Settings > Summarize Values By > Distinct Count. Distinct Count requires the Data Model; it is not a standard summary option for every ordinary PivotTable, and availability differs in Excel for Mac.

Blanks, errors, and text comparisons

Decide explicitly whether blanks should count. The opening formulas exclude empty cells, which is generally what a reader means when asking for the number of distinct entries in a data column. If a blank should count as one distinct value, do not filter it out; however, Excel may represent blanks in formula results in ways that affect counting, so test the result against a small sample. A cell containing a formula that returns an empty string ("") is not the same thing as a truly empty cell for every Excel operation. The FILTER condition in the modern formula excludes both truly empty cells and cells whose formula result is an empty string.

Errors in the source range can also affect results. UNIQUE or FILTER may pass an error through, and the COUNTIF/SUMPRODUCT calculation may fail when an error is part of the data. IFERROR around the entire modern formula turns any error into zero, which is convenient when the only expected error is an empty filtered result. If source errors might signal bad records, handle or inspect them separately rather than automatically hiding every error as zero.

Excel’s usual text comparisons are case-insensitive for these formulas, and visually similar text can still be different values. For example, a trailing space in “Oak ” can make it distinct from “Oak,” while differences in capitalization generally do not. If results look unexpectedly high, check for leading or trailing spaces, nonbreaking spaces copied from web pages, inconsistent spelling, and numbers stored as text. Functions such as TRIM can remove ordinary extra spaces, but imported nonbreaking spaces may need separate cleaning.

Choosing the right approach

Use the method that matches the workbook and the question:

  • Microsoft 365 or Excel 2021 and later: use UNIQUE with FILTER for a readable, nonblank distinct count, or use UNIQUE alone when you need the list.
  • A workbook that needs a row-by-row audit trail: use the COUNTIF helper column and sum its first-occurrence flags.
  • An older workbook with no dynamic-array functions: use the SUMPRODUCT/COUNTIF formula if its case-insensitive and wildcard behavior fits the data.
  • A report grouped by category: use a filtered UNIQUE formula for a quick answer, or a Data Model PivotTable for a reusable summary.
  • Case-sensitive or unusual text rules: clean and define the comparison rules first; ordinary COUNTIF and UNIQUE-based counting should not be treated as case-sensitive.

Before distributing a report, compare the result with a small hand-checked sample. Confirm whether blanks count, whether formulas returning empty strings exist, and whether numbers stored as text should be considered the same as numeric values. A distinct count is only meaningful once those data rules are clear.

Frequently Asked Questions

Can COUNTIF count unique values by itself?

COUNTIF counts how many cells meet a criterion; it does not provide a single built-in “count distinct” mode. You can combine it with SUMPRODUCT as shown above, or use it in a helper column to flag the first occurrence of each value.

How do I count unique values but ignore blanks?

In Microsoft 365 or Excel 2021 and later, with values in A2:A100, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0). FILTER removes blanks before UNIQUE returns the distinct entries, and ROWS counts them.

Does Excel’s UNIQUE function count a value that appears more than once?

Yes. UNIQUE returns one copy of each different value by default, even if that value occurs many times. Its optional exactly_once argument changes the behavior to return only values that appear once; that is a different question from counting distinct values.

How can I count distinct customers for each region?

Filter the customer range by the region and exclude blank customer cells, then count the UNIQUE results. The category formula above uses regions in A2:A100, customers in B2:B100, and the chosen region in E2; a Data Model PivotTable is another option for a repeatable grouped report.

Why does my COUNTIF distinct formula give the wrong answer?

Check for blanks, errors, wildcard characters such as * and ?, extra spaces, and values that are stored inconsistently as text or numbers. Also remember that COUNTIF is case-insensitive. If the workbook supports it, the UNIQUE-and-FILTER formula is often easier to verify against a visible list of results.

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.