While working in Excel, we often have to sort out the unique values from a set of data. Sometimes we have to count the number of equal values in a set of data.

Today, I will be showing how to count the unique values in a data set using the **COUNTIFS** function.

**Download Practice Workbook**

**COUNTIFS Unique Values in Excel**

Here weâ€™ve got a data set with some products and contact addresses of the customers who bought the products of a company called Mars Group.

Our objective here is to first count the total number of unique text values and numerical values from the contact addresses using the **COUNTIFS** function of Excel.

**1. Counting Unique Text Values**

First of all, we will count the number of unique text values from the contact addresses using the **COUNTIFS** function.

We will use a combination of the **SUM**,** ISTEXT,** and **COUNTIFS** functions of Excel.

The formula will be:

`=SUM(--(ISTEXT(C4:C20)*COUNTIFS(C4:C20,C4:C20)=1))`

**[**Itâ€™s an **Array Formula**. So donâ€™t forget to press **Ctrl + Shift + Enter** unless you are in **Office 365**.]

- Here
**C4:C20**is the range of my cells. You use your one. - You can perform the same operation using the
**COUNTIF**function of Excel.

See, there are a total of 3 unique text addresses.

**Explanation of the Formula**

returns`ISTEXT(C4:C20)`

**TRUE**for all the addresses that are text values and returns**FALSE**for all the addresses that are not text values.- Similarly,
returns`COUNTIFS(C4:C20,C4:C20)=1`

**TRUE**for all the addresses that appear only once, and**FALSE**for the addresses that appear more than once. multiplies the two conditions and returns 1 if both the conditions are met, otherwise returns 0.`--(ISTEXT(C4:C20)*COUNTIFS(C4:C20, C4:C20)=1)`

- Finally, the
**SUM**function adds all the values and returns the number of unique text values.

**Read more:**** How to Use COUNTIF for Unique Text**

**2. Counting Unique Numerical Values**

We can also count the number of unique numerical values from the contact addresses using the **COUNTIFS** function.

We will use a combination of the **SUM**, **ISNUMBER,** and **COUNTIFS** functions of Excel.

The formula will be:

`=SUM(--(ISNUMBER(C4:C20)*COUNTIFS(C4:C20,C4:C20)=1))`

**[**Itâ€™s also an **Array Formula**. So donâ€™t forget to press **Ctrl + Shift + Enter** unless you are in **Office 365**.]

- Here
**C4:C20**is the range of my cells. You use your one. - You can perform the same operation using the
**COUNTIF**function of Excel.

See, there are a total of 5 unique numerical addresses.

**Explanation of the Formula**

returns`ISNUMBER(C4:C20)`

**TRUE**for all the addresses that are numerical values and returns**FALSE**for all the addresses that are not numerical values.- Similarly,
returns`COUNTIFS(C4:C20,C4:C20)=1`

**TRUE**for all the addresses that appear only once, and**FALSE**for the addresses that appear more than once. multiplies the two conditions and returns 1 if both the conditions are met, otherwise returns 0.`--(ISNUMBER(C4:C20)*COUNTIFS(C4:C20, C4:C20)=1)`

- Finally, the
**SUM**function adds all the values and returns the number of unique numerical values

**Similar Readings:**

**How to Count Unique Values in Excel Using Pivot Table****Excel Formula Count Unique Values (3 Easy Ways)**

**3. Counting Unique Case-Sensitive Values**

The **COUNTIF** and **COUNTIFS** functions return case-insensitive matches. Therefore, to apply a case-sensitive match, we have to be a bit trickier.

Look at this new data set. Here we have a record of the grades of some students in the examination in a school called Sunflower Kindergarten.

We want to count the total number of unique grades here, considering case-sensitive matches.

To do that, take a new column and enter this formula in the first cell of the new column:

`=SUM(--EXACT($C$4:$C$20,C4))`

**[Array Formula. **So press **Ctrl + Shift + Enter**.]

- Here
**$C$4:$C$20**is the range of my cells and**C4**is my first cell. You use your one. - Donâ€™t forget to use the
**Absolute Cell Reference**.

Then drag the** Fill Handle** to copy this formula to the rest of the cells.

Then in a new cell, insert this formula:

`=SUM(IF(E4:E20=1,1,0))`

**Array Formula**. So press

**Ctrl + shift + Ente**r unless you are in

**Office 365**.]

- Here
**E4:E20**is the range of my new column. You use your one.

Here weâ€™ve got the number of grades that appear only once, Which is 4.

**Limitations of the Formulas and Alternative Option**

Up till now, we have used three methods to count the number of unique values in Excel.

But if you are a bit clever, you should realize by now that there are a few limitations to the tricks that weâ€™ve used.

That is, the formulas count the values that appear only once, but donâ€™t count the total number of actual unique values present upon there considering all the values.

For example, if the range of values contains **{A, A, A, B, B, C, D, E}**, it will count only **C, D, E,** and return **3**.

But sometimes someone may need to count **A, B, C, D, E** and return **5.**

To solve these types of problems, Excel provides a function called **UNIQUE**.

But a short reminder, that is available in **Office 365** only.

**Counting Unique Values Using the UNIQUE and the ROWS Functions**

In our original data set, to count the unique number of contact addresses considering all the addresses, you can use this formula:

`=COUNT(UNIQUE(C4:C20))`

See, there are a total of 6 unique addresses, considering all the addresses at least once.

Now, to find the unique text addresses only, you can use this formula:

`=ROWS(UNIQUE(IF(ISTEXT(`

`C4:C20`

`),`

`C4:C20`

`)))-1`

**C4:C20**is my range of values. You use your one.- Use the
**ROWS**function in place of the**COUNT**function. - And donâ€™t forget to subtract 1 from the formula at the end.

Similarly, to find the unique numerical addresses only, you can use this formula:

`=ROWS(UNIQUE(IF(ISNUMBER(`

`C4:C20`

`),`

`C4:C20`

`)))-1`

**Conclusion**

Using these methods, you can count the number of unique values in a data set. Do you know any other method? Or do you have any questions? Feel free to ask us.