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.
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.
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. Using COUNTIFS for Counting Unique Text Values in Excel
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.
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 with COUNTIFS Function
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.
You can perform the same operation using the COUNTIF function in Excel. See, there are a total of 5 unique numerical addresses.
3. Counting Unique Case-Sensitive Values Using COUNTIFS in Excel
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.
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.
Immediately, it copies the formula to the lower cells, and the remaining cells get outputs in a second.
- 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.
It’s also an array formula. Here we’ve got the number of grades that appear only once, which is 4.
4. Inserting COUNTIFS to Count Unique Values with Multiple Criteria
We can use multiple criteria to count the unique values. Let’s see the dataset first.
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.
Limitations of the COUNTIFS Function to Count Unique Values in Excel
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 Excel
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))
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)))-1In 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.
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
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.
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.
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.
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.
You may download the following Excel workbook for better understanding and practice 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
- How to Count Unique Values in Filtered Column in Excel
- How to Count Unique Values in Excel with Multiple Criteria
- How to Count Unique Values in Multiple Columns in Excel
- How to Count Unique Values Based on Criteria in Another Column in Excel
- Count Unique Values with Criteria by COUNTIFS in EXCEL
- Excel VBA: Count Unique Values in a Column
- How to Count Unique Text Values with Criteria in Excel
- Excel SUMPRODUCT Function to Count Unique Values with Criteria
- How to Count Unique Names in Excel
