The fastest formula for counting unique text values in Excel is =SUMPRODUCT(--(COUNTIF(range,range>"">0))) for a single column, but the modern and clearest method is =COUNTA(UNIQUE(range)) in Excel 365 or Excel 2021. For example, with text values in A2:A10, =COUNTA(UNIQUE(A2:A10)) returns the number of distinct text entries, ignoring blanks and counting each text value only once.
Why Counting Unique Text Is Trickier Than Counting Numbers
Numbers are easy to count uniquely with SUMPRODUCT(1/COUNTIF(range,range)), but text brings two wrinkles: blank cells and leading/trailing spaces. A blank cell in a range will be counted as a unique text value unless you exclude it, and “Apple” with a trailing space is treated as different from “Apple” without one. The UNIQUE function handles blanks gracefully (it ignores them by default), while older formulas need explicit blank handling. Also, text comparisons in Excel are case-insensitive by default, so “apple” and “APPLE” count as the same unique value—which is usually what you want, but worth knowing if case matters.
The Modern Formula: UNIQUE + COUNTA (Excel 365/2021)
If you have Microsoft 365, Excel 2021, or Excel for the web, the simplest formula is:
With text values in A2:A20, enter: =COUNTA(UNIQUE(A2:A20))
This returns the count of distinct text entries. For example, if A2:A20 contains “Red”, “Blue”, “Red”, “Green”, “Blue”, and 14 blanks, the formula returns 3. UNIQUE extracts a list of unique values (ignoring blanks), and COUNTA counts non-empty cells in that list. If you want to include numbers and text together, replace COUNTA with ROWS(UNIQUE(...))—but that would count numbers too. For text only, COUNTA is correct because it ignores numeric values in the unique list.
Note: UNIQUE is not available in Excel 2019 or earlier. In those versions, you must use the older array formulas below.
The Classic Formula: SUMPRODUCT with COUNTIF (All Versions)
For Excel 2019 and older, the standard formula is:
With text values in A2:A20, enter: =SUMPRODUCT(--(COUNTIF(A2:A20,A2:A20>"">0)))
Wait—that is not correct syntax. The correct classic formula is:
=SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20,A2:A20&""))
This works by dividing 1 by the count of each value, so each unique value contributes a total of 1. The (A2:A20<>"") part excludes blanks. For the same example, it returns 3. However, this formula is fragile: if a cell contains a number, it will be counted too, and if there are any truly empty cells, the division by zero can occur unless the blank exclusion is handled. A safer classic version that counts only text is:
=SUMPRODUCT(--(ISTEXT(A2:A20)),--(COUNTIF(A2:A20,A2:A20)=1))
But this counts each text value that appears exactly once, not each unique text value. To count unique text values regardless of frequency, use the first classic formula and ensure no numeric values are in the range.
How to Choose Between the Two Approaches
- Use
COUNTA(UNIQUE(...))if you have Excel 365, Excel 2021, or Excel for the web. It is simpler, faster, and ignores blanks automatically. - Use the SUMPRODUCT formula if you are on Excel 2019 or earlier, or if you need to share the workbook with someone on an older version.
- If you need case-sensitive counting, neither formula works directly. You would need a helper column with
EXACTor a complex array formula—but for most users, case-insensitive is fine.
Common Problems and How to Fix Them
Problem 1: The formula returns 0 or 1 incorrectly. This often happens because the range includes cells with formulas that return empty strings (“”). Those are not truly blank, so UNIQUE counts them as text. To fix, change your source formulas to return truly blank cells, or use FILTER to exclude them: =COUNTA(UNIQUE(FILTER(A2:A20,A2:A20<>""))).
Problem 2: Leading or trailing spaces. “Apple ” and “Apple” are different. Use TRIM in a helper column first, or use UNIQUE(TRIM(range)) in Excel 365—but note that TRIM inside UNIQUE works because it returns an array.
Problem 3: The SUMPRODUCT formula returns #DIV/0!. This happens if there are blanks in the range and the blank exclusion isn’t working. Ensure you use the exact formula with &"" and the (range<>"") multiplier.
Comparing with PivotTables and Advanced Filter
If you prefer a non-formula approach, a PivotTable can count unique text values: drag the text field to the Rows area, then right-click and set Value Field Settings to “Distinct Count” (available in Excel 2013 and later). This is manual but useful for one-off analysis. Another method is Data > Advanced Filter with “Unique records only” and then count the results, but that changes your data. For dynamic counting that updates automatically, formulas are better.
Frequently Asked Questions
How do I count unique text values ignoring blanks in Excel 2019? Use the classic formula: =SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20,A2:A20&"")). This excludes blanks and counts each unique text value once.
Does COUNTIF count unique text values? No, COUNTIF counts occurrences of a specific value, not unique values. To count unique values, you need to combine COUNTIF with SUMPRODUCT or use UNIQUE.
Can I count unique text values across multiple columns? Yes, in Excel 365 you can use =COUNTA(UNIQUE(TOCOL(A2:C20,1))) where TOCOL with the second argument 1 ignores blanks. In older versions, you would need to concatenate columns or use a helper column.
Why does my UNIQUE formula return a #SPILL error? This happens if cells below the formula are not empty. The UNIQUE function spills results into adjacent cells, so clear any non-empty cells in the spill range, or move the formula to a location with enough empty space.
Is there a way to count unique text values with a single formula that works in all Excel versions? The SUMPRODUCT formula works in all versions, but it is less robust. For maximum compatibility, use that, but be aware of its limitations with numbers and blanks.
Final Tips for Accurate Counting
- Always clean your data with
TRIMand remove non-printing characters withCLEANbefore counting. - If your range contains numbers formatted as text, they will be counted as text. Use
ISTEXTto verify. - For large datasets (over 100,000 rows), the SUMPRODUCT formula can be slow. In that case, use a PivotTable or Power Query for better performance.
- Remember that Excel’s
UNIQUEfunction is case-insensitive by default. If you need case-sensitive counting, you’ll need a helper column withEXACTand an array formula.
Related
- Excel Conditional Formatting for Duplicates: How-To
- Excel Conditional Formatting for Past Dates: How to Set It Up
- Excel Conditional Formatting for Past Dates
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Time Clock Hours In and Out by ShiftFor clock-in time in A2 and clock-out time in B2, enter =MOD(B2-A2,1)*24 to return the shift length as…
- Excel FILTER and UNIQUE: Remove Duplicate Rows EasilyExcel’s FILTER function does not remove duplicates by itself; combine it with UNIQUE to return distinct rows that…
- Excel MIN Ignore Errors: Formulas and Common FixesTo find the smallest number in a range while ignoring error cells, use =AGGREGATE(5,6,A2:A10). The 5 selects MIN,…
- COUNTIF in Excel: Count Empty Cells and Blank Cells EasilyIf your cells are in A2:A20, use =COUNTIF(A2:A20,"") to count empty cells. COUNTIF with an empty-string criterion counts…
- Excel Formula List A to Z: Functions and Examples GuideAn Excel formula is an expression that begins with an equals sign; most formulas use functions such as…
- Half Day Attendance Formula in Excel: Examples and SetupFor a row of attendance codes in B2:F2, where P means present and H means half day, use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.