The fastest way to count unique text values in Excel is =SUMPRODUCT((COUNTIF(range,range)>0)/COUNTIF(range,range)) for Excel 2019 and earlier, or =COUNTA(UNIQUE(range)) if you have Excel 365 or Excel 2021. The UNIQUE function is simpler and more reliable, but the SUMPRODUCT formula works in every version.
Why Counting Unique Text Is Harder Than It Looks
Excel has COUNTIF and COUNTIFS for counting cells that match a condition, but neither counts distinct values directly. A list like “Apple, Banana, Apple, Cherry” has 4 entries but only 3 unique items. The classic COUNTIF approach counts every occurrence, so you need a formula that first identifies which values appear more than once and then counts only the first occurrence of each.
The UNIQUE function, introduced in Excel 365, returns a list of distinct values from a range. Wrapping it in COUNTA gives the count. For older versions, the SUMPRODUCT formula uses array mathematics to achieve the same result without pressing Ctrl+Shift+Enter.
Using UNIQUE in Excel 365 and 2021
If you have Microsoft 365 or Excel 2021, the formula is straightforward. Assume your text values are in A2:A20. Enter:
=COUNTA(UNIQUE(A2:A20))
This returns the number of distinct text entries. For example, if A2:A20 contains “Red”, “Blue”, “Red”, “Green”, “Blue”, “Blue”, the formula returns 3. UNIQUE ignores empty cells by default, so you don’t need to filter them out.
If you want to count unique values that meet a condition, use UNIQUE with FILTER. For instance, to count unique products sold in the “North” region (with region in B2:B20 and product in A2:A20):
=COUNTA(UNIQUE(FILTER(A2:A20, B2:B20="North")))
This works in Excel 365 and 2021, but not in older versions.
The Classic SUMPRODUCT Formula for Older Excel
For Excel 2019, 2016, and earlier, the standard formula is:
=SUMPRODUCT((COUNTIF(A2:A20, A2:A20)>0)/COUNTIF(A2:A20, A2:A20))
How it works: COUNTIF(A2:A20, A2:A20) returns an array of counts for each cell. For “Red” appearing twice, it returns {2;2;1;1;…}. Dividing 1 by each count gives fractions that sum to 1 per unique value. The >0 part ensures that any value with a count of zero (which never happens in a range) is excluded. The result is the number of unique text values.
This formula treats numbers and text the same way, but if your range contains both, it counts both. To count only text (excluding numbers and blanks), you can add an ISTEXT check:
=SUMPRODUCT((ISTEXT(A2:A20))/COUNTIF(A2:A20, A2:A20&""))
Note the &"" to avoid division by zero if there are blanks.
Handling Blank Cells and Errors
Blanks can cause problems. The SUMPRODUCT formula with COUNTIF will treat a blank cell as a unique value, so if your range has empty cells, you’ll get a higher count than expected. To ignore blanks, use:
=SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20, A2:A20&""))
This divides 1 by the count of each value, but only for non-empty cells. The &"" converts blanks to empty strings so COUNTIF doesn’t return zero.
If your data contains errors like #N/A, the UNIQUE function will return them as values, and COUNTA will count them. To exclude errors, wrap the range in IFERROR:
=COUNTA(UNIQUE(IFERROR(A2:A20, "")))
This replaces errors with empty text, which UNIQUE ignores.
Case Sensitivity and Text Trimming
By default, Excel treats “apple” and “APPLE” as the same for counting purposes, because COUNTIF and UNIQUE are not case-sensitive. If you need to count them as different, you must use an array formula with EXACT. For example, in Excel 365:
=COUNTA(UNIQUE(EXACT(A2:A20, A2:A20)*1))
This is tricky because EXACT returns TRUE/FALSE, and multiplying by 1 converts to 1/0, but UNIQUE will then treat 1 and 0 as values, not the original text. A better approach is to use a helper column with EXACT and then count unique values of that column, or use a more advanced array formula. For most users, case-insensitive counting is what they want.
Leading or trailing spaces can also make “Apple” and “Apple ” count as different. Use TRIM to clean data first:
=COUNTA(UNIQUE(TRIM(A2:A20)))
This removes extra spaces before counting.
Comparing UNIQUE, SUMPRODUCT, and PivotTables
There are three main ways to count unique text values in Excel. UNIQUE is the easiest and most readable, but only works in Excel 365 and 2021. The SUMPRODUCT formula works everywhere but is harder to understand and can be slow on very large datasets (over 10,000 rows). A PivotTable is another option: add your text column to the Rows area, then right-click and select “Value Field Settings” > “Distinct Count”. This works in all modern Excel versions, but it’s manual and not a formula.
For large data, UNIQUE is faster because it’s a native function. The SUMPRODUCT formula recalculates every time and can slow down your workbook if used on thousands of rows. If you need a dynamic count that updates automatically, a formula is better than a PivotTable.
Common Mistakes and Troubleshooting
One frequent error is using =COUNTIF(A2:A20, "unique") which counts cells equal to the word “unique”, not distinct values. Another is forgetting that UNIQUE returns an array, so you must wrap it in COUNTA or ROWS to get a single number. If you see a #VALUE! error with SUMPRODUCT, it usually means your range includes entire columns (like A:A) which causes performance issues or errors. Always use a specific range like A2:A1000.
If you get a #NAME? error, your Excel version doesn’t support UNIQUE. Check your version by going to File > Account. If you have Excel 2019 or earlier, use the SUMPRODUCT formula.
Frequently Asked Questions
How do I count unique text values in a column with blanks?
Use =COUNTA(UNIQUE(A2:A20)) in Excel 365 because UNIQUE ignores blanks. In older Excel, use =SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20, A2:A20&"")). The &"" prevents division by zero.
Can I count unique text values based on a condition?
Yes, in Excel 365 use =COUNTA(UNIQUE(FILTER(A2:A20, B2:B20="Yes"))). In older versions, you need an array formula like =SUMPRODUCT((B2:B20="Yes")*(A2:A20<>"")/COUNTIFS(A2:A20, A2:A20, B2:B20, "Yes")).
Why does my UNIQUE formula return 0?
If COUNTA(UNIQUE(range)) returns 0, the range is either empty or contains only blanks. Check that your range includes non-empty cells. Also, if you used UNIQUE on a range with errors, it might return an error, not 0.
Does the SUMPRODUCT formula work with numbers?
Yes, it counts both numbers and text. If you want only text, add ISTEXT as shown above. For numbers only, replace ISTEXT with ISNUMBER.
How do I count unique values in a column with multiple criteria?
In Excel 365, use =COUNTA(UNIQUE(FILTER(A2:A20, (B2:B20="North")*(C2:C20="Active")))). In older Excel, use a SUMPRODUCT with COUNTIFS for multiple conditions.
Is there a way to count unique text values without a formula?
Yes, use a PivotTable. Drag the text column to Rows, then right-click the values area and choose “Value Field Settings” > “Distinct Count”. This works in Excel 2013 and later.
Related
- Excel Conditional Formatting for Past Dates: How to Set It Up
- Excel Conditional Formatting for Past Dates
- Excel Conditional Formatting Duplicate Rows: Full Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for the Lowest Value: MIN ExplainedThe Excel formula for the lowest value in a range is =MIN(A1:A10). Replace A1:A10 with the cells you…
- How to Shuffle Cells in Excel: Formulas and Safe StepsYes. Excel has no single Shuffle command, but you can randomize a list or whole rows by adding…
- INDEX and VLOOKUP in Excel: How They Work and CompareINDEX and VLOOKUP are both Excel lookup tools: VLOOKUP searches the leftmost column of a range and returns…
- Excel IFS Function: Syntax, Examples, and Common ErrorsExcel’s IFS function contains pairs of logical tests and the values to return when those tests are true.…
- Excel CLEAN and TRIM Functions: Remove Hidden SpacesIn Excel, CLEAN removes many nonprinting control characters, while TRIM removes leading and trailing ordinary spaces and reduces…
- How to Add Parentheses in Excel Formulas CorrectlyTo add parentheses in an Excel formula, edit the cell, type an opening parenthesis where you want a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.