Count unique and unique distinct text and numerical values in a range

Unique cell values are greyed, in the picture above.

Array formula in D14:

=SUM(IF(COUNTIF(tbl, tbl)=1, 1, 0)) + CTRL + SHIFT + ENTER

Count unique and unique distinct text and numerical values in a range1

Unique distinct cell values are greyed, in the picture above.

Array formula in D26:

=SUM(IF(ISBLANK(tbl), 0, 1/(COUNTIF(tbl, tbl)))) + CTRL + SHIFT + ENTER

Count unique and unique distinct text and numerical values in a range2

Unique text cell values are greyed, in the picture above.

Array formula in D38:

=SUM(IF(ISTEXT(tbl)*COUNTIF(tbl, tbl)=1, 1/(COUNTIF(tbl, tbl)), 0)) + CTRL + SHIFT + ENTER

Count unique and unique distinct text and numerical values in a range3

Unique distinct text cell values are greyed, in the picture above.

Array formula in E50:

=SUM(IF(ISTEXT(tbl), 1/(COUNTIF(tbl, tbl)), 0)) + CTRL + SHIFT + ENTER

Count unique and unique distinct text and numerical values in a range4

Unique numerical cell values are greyed, in the picture above.

Array formula in E62:

=SUM(IF(ISNUMBER(tbl)*COUNTIF(tbl, tbl)=1, 1/(COUNTIF(tbl, tbl)), 0)) + CTRL + SHIFT + ENTER

Count unique and unique distinct text and numerical values in a range5

Unique distinct numerical cell values are greyed, in the picture above.

Array formula in E74:

=SUM(IF(ISNUMBER(tbl), 1/(COUNTIF(tbl, tbl)), 0)) + CTRL + SHIFT + ENTER

Named ranges

tbl (B6:F12)
What is named ranges?

How to implement array formula to your workbook

Change the named range.

Download excel file for this tutorial.

Count unique text values in a range containing both numerical and text values.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

ISNUMBER(value)
Checks whether a value is a number and returns TRUE or FALSE

ISBLANK(value)
Checks whether a reference is to an empty cell and returns TRUE or FALSE

SUM(number1,[number2],)
Adds all the numbers in a range of cells

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Filter unique text values from a range containing both numerical and text values in excel
  2. Create a unique distinct text list from a range containing both numerical and text values in excel
  3. Extract and sort text cells from a range containing both numerical and text values
  4. Extract duplicate text values from a range containing both numerical and text values in excel
  5. Count unique distinct text values in a range in excel
  6. Filter unique text values in a range using “contain” condition in excel
  7. Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel
  8. Count unique distinct values using date criteria in a range in excel
  9. Filter text values existing in range 1 but not in range 2 using array formula in excel
  10. Filter unique text values using “begins with” criterion in a range in excel