## Count unique distinct text values in a range

*Article last updated on February 06, 2018*

**Array formula in D14:**

Alternative array formula:

**Array formula in D15:**

**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

*.*

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

Count unique distinct values within same week, month or year

Introduction What are unique distinct values? Unique distinct values are all values but duplicates are merged into one value. Count […]

Filter unique distinct values, sorted and blanks removed

Table of Contents Filter unique distinct values, sorted and blanks removed Filter duplicate values, sorted and blanks removed EEK asks: […]

Create a unique distinct list where a corresponding column has text cell values

Question: I want a unique list to be created from a column where an adjacent column has text cell value? […]

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]

Create a unique distinct alphabetically sorted list

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

### 6 Responses to “Count unique distinct text values in a range”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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 https://www.facebook.com/office

Cheers,

Andy

MSFT Office Outreach Team

Thank you for your kind words! I´ll definitely check it out.

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!

No problem Oscar. Keep up the great posts!

Best,

Andy

MSFT Office Outreach Team

Great post! It totally saved my afternoon. I've been working with Excel for a decade and it still amazes me how much I don't know yet.

Nelson,

thank you for commenting!