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:

[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

• ISTEXT(C4:C20) returns TRUE for all the addresses that are text values and returns FALSE for all the addresses that are not text values.
• Similarly, COUNTIFS(C4:C20,C4:C20)=1 returns TRUE for all the addresses that appear only once, and FALSE for the addresses that appear more than once.
• --(ISTEXT(C4:C20)*COUNTIFS(C4:C20, C4:C20)=1)multiplies the two conditions and returns 1 if both the conditions are met, otherwise returns 0.
• Finally, the SUM function adds all the values and returns the number of unique text values.

---

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:

[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

• ISNUMBER(C4:C20) returns TRUE for all the addresses that are numerical values and returns FALSE for all the addresses that are not numerical values.
• Similarly, COUNTIFS(C4:C20,C4:C20)=1 returns TRUE for all the addresses that appear only once, and FALSE for the addresses that appear more than once.
• --(ISNUMBER(C4:C20)*COUNTIFS(C4:C20, C4:C20)=1) multiplies the two conditions and returns 1 if both the conditions are met, otherwise returns 0.
• Finally, the SUM function adds all the values and returns the number of unique numerical values

---

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:

[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:

[Again Array Formula. So press Ctrl + shift + Enter 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:

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:

• 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:

---

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.

The post COUNTIFS Unique Values in Excel appeared first on ExcelDemy.