To count unique text values in Excel, there is no single COUNTIF function that does it directly—you need to combine it with other functions. The most reliable formula for Microsoft 365 and Excel 2021 is =COUNTA(UNIQUE(A2:A100)) (adjust the range as needed), which returns the number of distinct text entries, ignoring blanks and duplicates. For older Excel versions, use the classic array formula =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&"")).
Why COUNTIF Alone Can’t Count Unique Values
COUNTIF counts cells that meet a single condition, like =COUNTIF(A2:A100,"apple") which counts every cell containing “apple”. It has no built-in option to count each distinct value only once. To get a unique count you must either remove duplicates first or use a formula that evaluates each value against the whole range and returns a weighted result. This is a common point of confusion because many users expect a “unique” argument in COUNTIF, but Microsoft never added one.
The Modern Solution: UNIQUE Function (Excel 365 and 2021)
If you have Excel for Microsoft 365, Excel 2021, or Excel 2024, the easiest method uses the UNIQUE function, which returns a list of distinct values from a range. To count them, wrap UNIQUE in COUNTA (to count non-empty cells) or in ROWS (to count rows).
- Count unique text values (non-empty):
=COUNTA(UNIQUE(A2:A100)) - Count unique values including blanks (rarely needed):
=ROWS(UNIQUE(A2:A100)) - Count unique values that appear only once (truly unique):
=COUNTA(UNIQUE(A2:A100,,TRUE))– the third argument TRUE returns values that appear exactly once.
For example, if A2:A10 contains “apple”, “banana”, “apple”, “cherry”, “”, “banana”, the formula =COUNTA(UNIQUE(A2:A10)) returns 3 (apple, banana, cherry). The blank cell is ignored by COUNTA.
On Mac, the same formulas work. In Excel for the web, UNIQUE is also available, so the same syntax applies.
Classic Formula for Older Excel Versions (2010–2019)
If you don’t have UNIQUE, use the array formula that divides 1 by each count and sums the reciprocals. Because each duplicate gets a fractional share, the total equals the number of distinct values.
With text values in A2:A100, enter:
=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))
This formula works because COUNTIF returns an array of counts for each cell. For a value that appears 3 times, each cell contributes 1/3, and the three cells sum to 1. The &"" part forces text handling to avoid errors with numeric-looking text. In older Excel, you must press Ctrl+Shift+Enter to enter it as an array formula (in Excel 2019 and earlier). In Excel 365, it works as a normal formula.
Example: A2:A6 = {“red”,”blue”,”red”,”green”,”blue”}. The formula returns 3. If you include a blank cell, it is excluded because of the (A2:A100<>"") condition.
How to Choose the Right Method
Your choice depends on your Excel version and whether you need to count only text or also numbers.
- Excel 365, 2021, or 2024: Use
=COUNTA(UNIQUE(range)). It’s simple, dynamic, and automatically spills the unique list if you want to see it. - Excel 2019 or earlier: Use the SUMPRODUCT array formula. It works but requires Ctrl+Shift+Enter in some versions and can be slow on very large ranges (over ~10,000 cells).
- If your range contains both text and numbers: COUNTA counts both text and numbers, which is usually what you want. If you need only text, combine with ISTEXT:
=SUMPRODUCT((ISTEXT(A2:A100))/COUNTIF(A2:A100,A2:A100&""))
For a one-off task, you can also use the Remove Duplicates feature (Data > Data Tools > Remove Duplicates) and then count the remaining rows, but that changes your data.
Common Problems and How to Fix Them
Several issues trip up users when counting unique text:
- Trailing spaces: “apple ” and “apple” are seen as different. Use
=TRIM(A2)in a helper column, or use the formula=COUNTA(UNIQUE(TRIM(A2:A100)))(in Excel 365, TRIM works on arrays). - Case sensitivity: COUNTIF and UNIQUE are case-insensitive by default. “Apple” and “apple” count as the same. If you need case-sensitive unique counts, use
=SUMPRODUCT((MATCH(TRUE,EXACT(A2:A100,TRANSPOSE(A2:A100)),0)=ROW(A2:A100)-ROW(A2)+1)*1)– but this is complex; consider using a helper column with=EXACT(A2,A1)logic. - Blanks counted as a value: COUNTA ignores blanks, but ROWS does not. Always use COUNTA with UNIQUE unless you want blanks counted.
- Errors in the range: If any cell contains #N/A, the formula may return an error. Use IFERROR inside the range or clean the data first.
- Large data sets: The SUMPRODUCT formula recalculates slowly on 50,000+ rows. Prefer UNIQUE if available.
Comparing COUNTIF with Other Counting Functions
Excel offers several counting functions, and it helps to know when to use each:
- COUNT: Counts numeric cells only.
=COUNT(A2:A100) - COUNTA: Counts non-empty cells (text, numbers, errors).
=COUNTA(A2:A100) - COUNTIF: Counts cells meeting a condition, e.g.,
=COUNTIF(A2:A100,"apple"). - COUNTIFS: Counts cells meeting multiple conditions across ranges, e.g.,
=COUNTIFS(A2:A100,"apple",B2:B100,">10"). - UNIQUE + COUNTA: Counts distinct values, as described above.
Note that COUNTIF and COUNTIFS are case-insensitive and treat text and numbers separately. They also ignore blank cells unless you count them with a condition like "".
Alternatives for Counting Unique Text
If you prefer not to use formulas, or if you need a dynamic list of unique values, consider these alternatives:
- PivotTable: Insert a PivotTable (Insert > PivotTable), drag the text field to Rows, and then use the row count. To see the count, right-click the row field and select Field Settings > Subtotals & Filters > Count. This gives a list of unique items with their counts.
- Advanced Filter: Select your data, go to Data > Sort & Filter > Advanced, choose "Copy to another location", check "Unique records only", and specify a destination. Then use
=COUNTA(destination)to count the filtered list. - Power Query: In Excel 365, go to Data > Get Data > From Table/Range, then in the Power Query editor, use the Remove Duplicates command and count the rows. This is ideal for large datasets.
- VBA macro: For a one-click solution, a macro can loop through a range, add items to a Collection or Dictionary, and display the count. However, macros are not undoable, so always save a copy of your workbook before running.
Each method has trade-offs: PivotTables are interactive, Advanced Filter is quick but static, Power Query is powerful but requires a few steps, and VBA is overkill for most users.
Frequently Asked Questions
How do I count unique text in Excel ignoring blanks?
Use =COUNTA(UNIQUE(A2:A100)) in Excel 365. COUNTA ignores empty cells, so blanks are automatically excluded. In older Excel, the SUMPRODUCT formula with (A2:A100<>"") also ignores blanks.
Can I count unique text with a condition (e.g., only for a certain category)?
Yes, but you need to combine UNIQUE with FILTER. For example, if A2:A100 has text and B2:B100 has categories, to count unique text where B is "Fruit": =COUNTA(UNIQUE(FILTER(A2:A100,B2:B100="Fruit"))). This works in Excel 365 and 2021. In older versions, you'd need a more complex array formula.
Why does my COUNTIF formula return 0 for unique text?
If you're using =COUNTIF(range,uniquevalue) and it returns 0, the value may not exist exactly as typed (check spaces, case, or special characters). Also, COUNTIF cannot count unique values by itself; it only counts occurrences of a specific value. For a unique count, use the formulas above.
Is there a way to count unique text in Excel for the web?
Yes, Excel for the web supports UNIQUE and COUNTA, so the same =COUNTA(UNIQUE(A2:A100)) formula works. The SUMPRODUCT formula also works in the web version, but you don't need Ctrl+Shift+Enter.
What is the difference between "unique" and "distinct" in Excel?
In Excel, "distinct" means each different value is counted once, regardless of how many times it appears. "Unique" (as in UNIQUE's third argument TRUE) means values that appear only once in the range. For example, in {"apple","apple","banana"}, distinct count is 2, but unique count is 1 (only banana appears once).
My data has numbers and text mixed. How do I count unique text only?
Use =SUMPRODUCT((ISTEXT(A2:A100))/COUNTIF(A2:A100,A2:A100&"")) in older versions. In Excel 365, you can use =COUNTA(UNIQUE(FILTER(A2:A100,ISTEXT(A2:A100)))) to exclude numbers.
Related
- Excel and Access Training: What You Actually Need
- Convert KMZ to Excel: 4 Ways That Actually Work
- DepEd Class Attendance Sheet Template Excel: Free Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIFS in Excel: How to Count OR CriteriaCOUNTIFS in Excel uses AND logic: a row is counted only when it meets every condition you supply.…
- C++ String Length: Choosing the Right Function for TextFor a C++ std::string, call length() or size(); they return the same number. For a null-terminated C-style character…
- Excel Data Validation List Formula: Examples and FixesFor an Excel drop-down list, select the cells, choose Data > Data Validation, set Allow to List, and…
- Excel Filter Drop-Down Shortcut: Windows and MacIn Excel for Microsoft 365 on Windows, select a cell in the filtered column’s header and press Alt+Down…
- Search Multiple Values in Excel Filter: A Step-by-Step GuideTo filter a column for several values in Excel for Microsoft 365 on Windows, open its filter dropdown,…
- Excel Formula to Convert Minutes to HoursTo convert a number of minutes in cell A2 to decimal hours in Microsoft Excel, divide by 60:…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.