Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

COUNTIFS Unique Values in Excel (4 Easy Ways)

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. If you are facing problems with this issue, this article is obviously for you. In this article, we’ll demonstrate 4 easy and quick methods to use the COUNTIFS function to count unique values in Excel. So, let’s go through the entire article to understand the topic properly.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


4 Methods to Use COUNTIFS Function to Count Unique Values in Excel

For ease of understanding, we’re going to use a Customer Record of Mars Group. This dataset includes some Product Names and Contact Addresses of the Customers who bought the products from this company.

countifs unique values

Note that this is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset. 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 in Excel.
Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.


1. Counting Unique Text Values

In our first method, 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. To do this using the first method, you may follow the below steps.

📌 Steps:

  • At the very beginning, select cell B24 and enter the following formula.
=SUM(--(ISTEXT(C5:C21)*COUNTIFS(C5:C21,C5:C21)=1))

Here, C5:C21 is the range of my cells. You use yours.

Formula Breakdown

  • ISTEXT(C5:C21) 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.
  • Then, press the ENTER key.

Counting Unique Text Values

Note: It’s an Array Formula. So don’t forget to press CTRL + SHIFT + ENTER unless you are operating in Office 365.
Moreover, you can perform the same operation using the COUNTIF function in Excel. As you can see, there are three distinct text addresses.

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. It’s simple and easy; just follow along.

📌 Steps:

  • At first, go to cell B24 and insert the formula below.
=SUM(--(ISNUMBER(C5:C21)*COUNTIFS(C5:C21,C5:C21)=1))

Here, C5:C21 is the range of my cells. You may use yours.

Formula Breakdown

  • ISNUMBER(C5:C21) returns TRUE for all the addresses that are numerical values and returns FALSE for all the addresses that are not numerical values.
  • Similarly, COUNTIFS(C5:C21,C5:C21)=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.
  • After that, tap the ENTER key.

Counting Unique Numerical Values

You can perform the same operation using the COUNTIF function in Excel. See, there are a total of 5 unique numerical addresses.


Similar Readings:


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 an Examination Record of Sunflower Kindergarten. It contains the grades of some students in the examination at this school.

Counting Unique Case-Sensitive Values

We want to count the total number of unique grades here, considering case-sensitive matches. Let’s explore the method step by step.

📌 Steps:

  • To do that, construct a new column with the heading Frequency to Each Grade and enter the following formula in the first cell (cell E5) of this column.
=SUM(--EXACT($C$5:$C$21,C5))

Here, C5:C21 is the range of my cells, and C5 is my first cell. You could use yours. Don’t forget to use the Absolute Cell Reference.

  • As usual, press ENTER.

  • Now, bring the cursor to the right-bottom corner of cell E5 and it’ll look like a plus (+) sign. Actually, it’s the Fill Handle tool.
  • Following this, double-click on it.

Using Fill Handle to Count Unique Values

Immediately, it copies the formula to the lower cells, and the remaining cells get outputs in a second.

Count of each grade

  • Presently, create an output range in cells in the B23:E24 range.
  • After that, go to cell B24 and write down the following formula.
=COUNTIFS($E$5:$E$21,1)
  • Also, tap ENTER.

Using COUNTIFS Function

It’s also an array formula. Here we’ve got the number of grades that appear only once, which is 4.


4. Counting Unique Values with Multiple Criteria

We can use multiple criteria to count the unique values. Let’s see the dataset first.

COUNTIFS Unique Values with Multiple Criteria

So, here the criteria are Customer Name, Brand, and we will count the products if they fulfill those criteria. By using the COUNTIFS function, we will primarily count only those products whose customer names and brands are the same. So, let’s see the following steps.

📌 Steps:

  • Initially, select the cell where you want the result. So, we selected cell D24 and put down the formula below.
=COUNTIFS($D$5:$D$21,B24,$C$5:$C$21,C24)

Here, the range of cells D5:D21 indicates the Customer Name, and the criteria for this range is B24 which is Natasha. Also, the range of cells C5:C21 indicates the Brand, and the criteria for this range is C24 which is Samsung.

  • Then, press ENTER.

using COUNTIFS to count unique values with multiple criteria


Limitations of the COUNTIFS Function to Count Unique Values

Until now, we have used four 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 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, and E and return 5.
To solve these types of problems, Excel provides a function called the UNIQUE function. But a short reminder, that is available in Office 365 only.


Alternative Way: Using the UNIQUE and ROWS Functions to Count Unique Values

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

=COUNTA(UNIQUE(C5:C21))

Counting Unique Values Using the UNIQUE and ROWS Functions

See, there are a total of 12 unique addresses (text and number), considering all the addresses at least once.
Now, to find the unique text addresses only, you can use this formula:

=ROWS(UNIQUE(IF(ISTEXT(C5:C21),C5:C21)))-1

In this formula, C5:C21 is my range of values. You use yours. Also, use the ROWS function in place of the COUNTA function. And don’t forget to subtract 1 from the formula at the end.

Counting Unique Text Address Values

Similarly, to find the unique numerical addresses only, you can use the following formula in the same cell:

=ROWS(UNIQUE(IF(ISNUMBER(C5:C21),C5:C21)))-1

Counting Unique Numerical Address Values

That’s how you can easily get the number of unique addresses.


How to Count Distinct Values in Excel

Additionally, you can count distinct values in Excel. That means you have to count all the values at once. So, without further delay, let’s dive in.

📌 Steps:

  • Primarily, select cells in the C4:C21 range.
  • Secondarily, navigate to the Data tab.
  • On the Sort & Filter group of commands, select the Advanced filtering option.

Counting Distinct Values in Excel

Immediately, the Advanced Filter dialog box appears before us.

  • In the Action area, select Copy to another location.
  • Then, give the reference of C5:C21 in both the List range and Criteria range boxes.
  • Make sure to check the box of Unique records only.
  • Lastly, click OK.

Advanced Filter option in Excel

It’ll create a new range of cells in the E5:E16 range.

  • Afterward, go to cell E19 and paste the following formula.
=ROWS(E5:E16)
  • As always, press ENTER.

Counting Distinct Values in Excel

So, there are a total of 12 distinct address values in Column C.


Practice Section

For doing practice by yourself, we have provided a Practice section like the one below on each sheet on the right side. Please do it yourself.


Conclusion

This article explains how to use the COUNTIFS function to count unique values in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, Exceldemy, a one-stop Excel solution provider, to explore more.


Further Readings

 

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo