Count Unique Values with Criteria by COUNTIFS in EXCEL (4 Examples)

While working with data in Microsoft Excel, sometimes we need to find the unique values. The main goal of counting separate and special values is to distinguish them from duplicates in an Excel list. We can count the unique values with so many functions and different formulas for different purposes. In this article, we will count unique values with criteria by the COUNTIFS function in Excel.


Download Practice Workbook

You can download the workbook and practice with them.


Introduction to COUNTIFS Function

In Excel, the COUNTIFS function counts the number of cells in a range that match one of the provided conditions.

  • Syntax

The syntax for the COUNTIFS function is:

COUNTIFS (range1, criteria1, [range2], [criteria2], …)

  • Arguments

range1: [required] This is the first range to be evaluated.

criteria1: [required] The range1 criteria to employ.

range2: [optional] This is the second range to be evaluated.

criteria2: [optional] The range2 criteria to utilize.

  • Return Value

The total number of times a set of criteria has been met.


4 Different Examples to Count Unique Values with Criteria by COUNTIFS in Excel

To count unique values with criteria by using the COUNTIFS function in Excel, we are going to use the following dataset. The dataset contains some Product names in column B, the Brand of each product in column C, the names of the Customers who brought those products in column D, and the Contact Address for each customer in column E. Now, we want to count the unique values with different criteria, so, let’s demonstrate the examples of those by using this dataset.

4 Different Examples to Count Unique Values with Criteria by COUNTIFS in Excel


1. Estimate Unique Values Based on a Particular Criteria in Excel

We can count the number of unique values based on a single criterion by combining the SUM, IF, and COUNTIFS functions in Excel. So, let’s follow the procedure for this.

STEPS:

  • Firstly, select the cell where you want to count the unique values using the criteria. Se, we select cell H5.
  • Secondly, put the formula in that cell.
=SUM(IF(G5=$D$5:$D$13, 1/(COUNTIFS($D$5:$D$13, G5, $B$5:$B$13, $B$5:$B$13)), 0))
  • Finally, pressing Enter will show the result.

Excel Countifs Unique Values with Criteria

🔎 How Does the Formula Work?

G5=$D$5:$D$13: This will find the cells containing Jhon, as cell G5 contains Jhon.

COUNTIFS($D$5:$D$13, G5, $B$5:$B$13, $B$5:$B$13: For all addresses that exist just once, will return TRUE; for all addresses that repeat multiple times, return FALSE.

1/(COUNTIFS($D$5:$D$13, G5, $B$5:$B$13, $B$5:$B$13): This will divine the formula by 1 and returns 0.5.

IF(G5=$D$5:$D$13, 1/(COUNTIFS($D$5:$D$13, G5, $B$5:$B$13, $B$5:$B$13)), 0): This will compare the conditions of the formula are met or not if they are met then returns 1, 0 otherwise.

SUM(IF(G5=$D$5:$D$13, 1/(COUNTIFS($D$5:$D$13, G5, $B$5:$B$13, $B$5:$B$13)), 0)): This will count the total unique values.

Read More: How to Count Unique Values in Multiple Columns in Excel (5 Ways)


2. Multiple Criteria to Count Unique Excel Values

We can use multiple criteria to count the unique values. So, here the criteria are Customer Name, Brands, and we will count the products if they fulfill those criteria. Mainly by using the COUNTIFS function we will count only those products whose customer names and brands are the same. So, let’s see the following steps.

STEPS:

  • By the same token as before, select the cell where you want the result. So, we select cell I5.
  • Next, type the formula in that particular cell.
=COUNTIFS(D5:D13,G5,C5:C13,H5)
  • Now, press Enter.

Multiple Criteria to Count Unique Excel Values

Here, the range of cells D5:D13 indicates the Customer Name, and the criteria for this range is G5 which is Jhon. Also, the range of cells C5:C13 indicates the Brand, and the criteria for this range is H5 which is Asus.

Read More: How to Count Unique Names in Excel (5 Methods)


3. Different Number of Text Values Counting in Excel

We can count different numbers of unique text values by combining the SUM, ISTEXT, and COUNTIFS functions in Excel. Now, we will use the COUNTIFS function to count the number of distinct text values from the contact addresses. Here, the criteria are the text values of the Contact Address column. We will count the unique text address in cell G5. Let’s look at the procedure to do this.

STEPS:

  • First, select the cell where you want to count the unique values using the criteria which is the text value. So, we select cell G5.
  • Second, put the formula in that cell to show the result.
=SUM(--(ISTEXT(E5:E13)*COUNTIFS(E5:E13,E5:E13)=1))
  • Finally, press Enter. So, there are a total of 2 unique text values in there.

🔎 How Does the Formula Work?

ISTEXT(E5:E13): This will return TRUE for all addresses that are text values, will return FALSE otherwise.

COUNTIFS(E5:E13,E5:E13): Here, this will returns TRUE for all addresses that appear just once and will return FALSE  for all addresses that appear more than once.

ISTEXT(E5:E13)*COUNTIFS(E5:E13,E5:E13): This will multiply the two formulas and returns 1 if they are met, returns 0 otherwise.

SUM(--(ISTEXT(E5:E13)*COUNTIFS(E5:E13,E5:E13)=1)): This will return the unique text values.

NOTE: It’s a formula that uses an array. Unless you’re using Office 365, remember to hit Ctrl + Shift + Enter.

Read More: COUNTIFS Unique Values in Excel (3 Easy Ways)


4. Count Numeric Values That Aren’t the Same

We can use Excel SUM, ISNUMBER, and COUNTIFS functions in conjunction to count unique numeric values in excel. Here, the criteria are that the combination of those three functions will count only the numerical values from a range of cells. Now, let’s see the steps down.

STEPS:

  • In the beginning, choose the cell where you wish to count the unique values based on the numerical value as the criteria. As a result, we choose cell G5.
  • Second, enter the formula to display the result in that cell.
=SUM(--(ISNUMBER(E5:E13)*COUNTIFS(E5:E13,E5:E13)=1))
  • And, press the Enter button on your keyboard.

🔎 How Does the Formula Work?

ISNUMBER(E5:E13): For all the addresses that are numeric values, this will return TRUE, FALSE otherwise.

COUNTIFS(E5:E13,E5:E13): For all addresses that show just once, this will return TRUE and return FALSE  for all addresses that show more than once.

ISNUMBER(E5:E13)*COUNTIFS(E5:E13,E5:E13): This will multiply the  ISNUMBER formula & COUNTIFS formula. Then will return 1 if they are met, return 0 otherwise.

SUM(--(ISTEXT(E5:E13)*COUNTIFS(E5:E13,E5:E13)=1)): The unique number values will return.

Read More: Excel VBA: Count Unique Values in a Column (3 Methods)


Conclusion

Using those above methods you can count unique values with criteria in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo