Count Unique Strings in Excel: Formulas and Examples

Formulas & functions · Updated November 18, 2026

To count distinct, nonblank text strings in Excel for Microsoft 365, put your data in A2:A100 and use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,(ISTEXT(A2:A100))*(A2:A100<>"")))),0). It returns the number of different text values, counting repeated entries once and ignoring blank cells and empty strings.

What “unique” means in Excel

People use “unique” to mean two different things. Often they want the number of distinct strings: if a value appears several times, count it once. In a list containing Oak, Pine, Oak, and Maple, there are three distinct strings. That is what the formula above counts.

In Excel’s terminology, however, a value that is “unique” can also mean one that appears exactly once. In the same list, Pine and Maple appear once, but Oak does not. That result is two. This distinction matters because Excel’s UNIQUE function supports both interpretations; its default is to return distinct values, while an optional argument restricts the result to values that occur exactly once.

The word “strings” usually means text, such as product names, codes, or labels. The main formula checks for text with ISTEXT, so it does not count numeric values. If your column contains a mix of text and numbers and you want every distinct nonblank value, use the all-values version below instead.

Use a formula to count distinct text

Assume the text values are in A2:A100. Enter the following in an empty cell:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A100,(ISTEXT(A2:A100))*(A2:A100<>"")))),0)

FILTER keeps rows that contain text and are not empty; UNIQUE reduces those results to one of each distinct value; ROWS counts the resulting list. IFERROR returns zero if no qualifying text remains, rather than displaying an error when the source range has no matches.

For example, suppose A2:A9 contains Oak, Pine, Oak, a blank cell, pine, Maple, a formula returning an empty string, and Maple. The formula returns 3: Oak, Pine, and Maple. In standard Excel comparisons, text matching is not case-sensitive, so Pine and pine are treated as the same value for this count.

The example uses a fixed range so it is easy to follow. Replace A2:A100 with the location of your actual data. Include the full range you need, but avoid unnecessarily referencing entire columns in a calculation involving dynamic arrays; a bounded range or Excel Table can make a workbook easier to manage.

Count distinct values of any type

If the list may contain numbers as well as text, and you want to count every distinct nonblank entry, remove the ISTEXT condition. With values in A2:A100, use:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0)

This counts distinct nonblank values regardless of whether a value is text or a number. It is not the right choice when the question specifically means text strings only: for example, a numeric 42 is included in this version but excluded by the ISTEXT version. Decide what your data represents before choosing the formula.

A formula-generated empty string is another reason to be explicit about blanks. A cell containing a formula that displays nothing is not always the same as a truly unused cell. The condition A2:A100<>”” excludes both ordinary blank cells and cells whose formula result is an empty string from the filtered results.

Return the unique strings as a list

Sometimes the useful answer is not only a count, but also the actual names being counted. UNIQUE can spill a list of distinct values into the cells below its formula. With text in A2:A100, enter this in an empty cell:

=UNIQUE(FILTER(A2:A100,(ISTEXT(A2:A100))*(A2:A100<>"")))

The spilled results show the distinct text values, in the order they first appear in the source range. To count the visible list, you can place the ROWS-and-UNIQUE count formula elsewhere, or count the spilled range using a reference such as B2# if the list begins in B2. The # refers to the entire spilled range, so it adjusts when the list changes.

To sort the distinct list alphabetically, wrap the result in SORT. With the same source range, use:

=SORT(UNIQUE(FILTER(A2:A100,(ISTEXT(A2:A100))*(A2:A100<>""))))

SORT changes the display order, not the underlying definition of distinct. If you only need a number, sorting adds no benefit; if you need to review or share the names, a sorted list can make comparisons easier.

Count entries that occur exactly once

For the different question “How many text strings appear only one time?”, set UNIQUE’s exactly_once argument to TRUE. With text in A2:A100, use:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A100,(ISTEXT(A2:A100))*(A2:A100<>"")),FALSE,TRUE)),0)

The second argument, FALSE, says to compare items down the column rather than across columns; the third argument, TRUE, asks for values that occur exactly once. If the list is Oak, Pine, Oak, and Maple, this formula returns 2 because Pine and Maple occur once. The standard distinct-count formula returns 3 because it counts Oak as one distinct value despite its repetition.

This is a common source of surprising results. Before using a formula, ask whether duplicates should be collapsed into one count or excluded unless they occur only once. Those are both reasonable questions, but they produce different answers.

Excel version and compatibility

UNIQUE and FILTER are available in Microsoft 365 and Excel 2021 or later. They are dynamic-array functions: Excel places the result in the formula cell and spills additional results into adjacent cells when necessary. The count formula returns a single number, while a formula that returns a list needs clear cells below it for the spill range.

The steps and formulas above are for Excel for Microsoft 365 on Windows. The same functions are available in supported Microsoft 365 versions for Mac and Excel for the web, though the ribbon layout and some interactions can differ. These formulas use commas between arguments, as in the stated Windows setup; some regional Excel configurations use semicolons instead, depending on system and list-separator settings.

If a coworker using an older Excel release opens a workbook with these formulas, that version may not calculate UNIQUE or FILTER. A compatibility option is to add a helper column and count each first occurrence. This also makes the counting logic visible row by row.

Older Excel: count first occurrences with a helper column

For a case-sensitive text count in an older Excel version, assume the source strings are in A2:A100. In B2, enter this formula and copy it down alongside the data:

=IF(AND(ISTEXT(A2),A2<>"",SUMPRODUCT(--EXACT(A2,$A$2:A2))=1),1,0)

In an empty summary cell, sum the helper values. If the helper results are in B2:B100, enter:

=SUM(B2:B100)

For example, if the source contains Oak, Pine, Oak, and Maple, the helper marks the first Oak, Pine, and Maple with 1 and the repeated Oak with 0; the sum is 3. EXACT compares text with case sensitivity, so Pine and pine are treated as different strings here. This differs from the main UNIQUE approach, which uses Excel’s usual case-insensitive text matching.

There is no need to enter the helper formula as a special array formula in current desktop Excel. In older editions, behavior can depend on the exact release, so check the workbook’s calculation results if the formula will be shared across legacy versions. For a case-insensitive count or complicated data, a PivotTable or Power Query may be more suitable than maintaining a custom helper formula.

Common problems and checks

  • The formula shows #NAME? Your Excel version may not support UNIQUE or FILTER, or a function name may be misspelled. Use Microsoft 365 or Excel 2021 or later for the dynamic-array formula, or use a helper-column method.
  • The formula returns zero unexpectedly. Check that the source cells really contain text and that the range points to the intended column. A cell containing a number stored as a number is excluded by ISTEXT, even if it looks like a code.
  • The count seems too small. Excel normally treats uppercase and lowercase versions of text as the same for this purpose. Also inspect the data for leading or trailing spaces and inconsistent spelling; visually similar values can still be different strings.
  • The formula returns an error. Check the source range for error values such as #N/A. Errors can interrupt FILTER’s calculation. Correct or handle the source errors before relying on the count.
  • A list formula displays #SPILL!. Clear cells in the area where Excel needs to place the results. A merged cell or another value in the spill area can prevent a dynamic array from expanding.
  • The formula does not update as expected. Confirm that newly added rows are inside the referenced range. Converting the data to an Excel Table can help the range expand as rows are added.

Spaces are particularly worth checking when names come from different systems. “Oak” and “Oak ” contain different text because the second value has a trailing space. To clean a source column, you can use a separate helper column with TRIM, then count the cleaned results. TRIM removes ordinary extra spaces from text but does not remove every possible nonprinting character, such as some nonbreaking spaces copied from websites.

Use an Excel Table for growing lists

If new records are added regularly, select the data and choose Insert > Table, then confirm whether the table has headers. Give the table a clear name from the Table Design tab, and use a structured reference in place of the fixed cell range. For example, if the table is named Records and its text column is named Item, the count formula is:

=IFERROR(ROWS(UNIQUE(FILTER(Records[Item],(ISTEXT(Records[Item]))*(Records[Item]<>"")))),0)

Structured references are easier to understand than a long range address and generally include new table rows automatically. If your headers or table name differ, replace Records and Item with the names shown in your workbook. Table names and column names must match exactly.

Frequently Asked Questions

Does Excel count uppercase and lowercase strings separately?
Not with the standard UNIQUE-based formula. Excel’s normal text comparisons are case-insensitive, so “Blue” and “blue” count as one distinct value. Use a case-sensitive method such as the EXACT helper formula if letter case is meaningful in your data.

How do I count unique values while ignoring blanks?
Use FILTER to remove blanks before counting the UNIQUE results. For text only, the opening formula also checks ISTEXT; for mixed types, use the all-values formula in the section above. Both versions return zero when the range has no qualifying values.

What is the difference between unique and distinct in Excel?
In everyday use, “distinct” usually means each different value counted once, even if it is repeated. Excel’s UNIQUE function returns that result by default. Setting its exactly_once argument to TRUE instead returns only values that appear one time in the source.

Can I count unique strings in two columns?
Yes, but decide whether a pair of cells is one record or whether both columns should be combined into one list of values. UNIQUE applied to a two-column range finds distinct rows, meaning it counts combinations. To count values from both columns as one combined list, use VSTACK in Microsoft 365 or Excel 2024, then filter and count the combined results.

Why does my count differ from a PivotTable?
A PivotTable may include blanks, numbers, or differently cleaned text depending on its source and settings, while the formula may deliberately exclude non-text values. Compare the input range and blank handling, then check for spaces and case differences. A PivotTable’s distinct count option may also depend on adding the source data to the Data Model.

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.