Count unique distinct text values in a range in excel
Array formula in D14:
=SUM(IF(FREQUENCY(COUNTIF(tbl, "<"&IF(ISTEXT(tbl), tbl, "")), COUNTIF(tbl, "<"&IF(ISTEXT(tbl), tbl, "")))>0, 1, 0)) + CTRL + SHIFT + ENTER
Alternative array formula:
=SUM(IF(ISERROR(1/(IF(ISTEXT(tbl), COUNTIF(tbl, tbl), ""))), 0, 1/(IF(ISTEXT(tbl), COUNTIF(tbl, tbl), "")))) + CTRL + SHIFT + ENTER
Array formula in D15:
=SUM(IF(ISTEXT(tbl), IF(COUNTIF(tbl, tbl)=1, 1, 0), 0)) + CTRL + SHIFT + ENTER
Named ranges
tbl (B5:F11)
What is named ranges?
How to customize the formula to your excel spreadsheet
Change the named range.
Download excel example file.
Count unique distinct text values in a range.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition
IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
ISTEXT(value)
Checks whether a value is text, and returns TRUE or FALSE
SUM(number1,[number2],)
Adds all the numbers in a range of cells
FREQUENCY(data_array, bins_array)
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array.
Related posts:
- Create a unique distinct text list from a range containing both numerical and text values in excel
- Count unique text values in a range containing both numerical and text values
- Filter unique text values from a range containing both numerical and text values in excel
- Filter text values existing in range 1 but not in range 2 using array formula in excel
- Extract duplicate text values from a range containing both numerical and text values in excel
- Filter common text values in range 1 and in range 2 using array formula in excel
- Count unique distinct values using date criteria in a range in excel
- Extract and sort text cells from a range containing both numerical and text values
- Filter unique distinct text values in a range using “contain” condition in excel
- Filter unique distinct text values using “begins with” criterion in a range using array formula in excel



October 5th, 2009 at 11:44 pm
Wow, what a useful post! This is a great breakdown of how to count unique distinct values. The Excel community on Facebook could benefit from your knowledge and expertise. Check it out at http://www.facebook.com/office
Cheers,
Andy
MSFT Office Outreach Team
October 6th, 2009 at 9:27 am
Thank you for your kind words! I´ll definitely check it out.
October 8th, 2009 at 7:07 am
Oscar, your blogs are definitely one of kind! You have some great tutorials.
Its amazing how you construct those awesome array formulas.... Please post your articles everyday! lol!
October 12th, 2009 at 11:03 pm
No problem Oscar. Keep up the great posts!
Best,
Andy
MSFT Office Outreach Team