# Count Unique Values with Criteria using COUNTIFS in EXCEL – 4 Examples

## Introduction to COUNTIFS Function

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

• Syntax

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

• Arguments

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

criteria1: [required]Â  range1 criteria.

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

criteria2: [optional]Â  range2 criteria.

• Return Value

The total number of times a set of criteria is met.

The dataset containsÂ  Product names in column B, the BrandÂ in column C, Customers’ namesÂ in column D, and the Contact Address for each customer in column E.

### Example 1 – Estimate Unique Values Based on a single Criterion in Excel

To count the number of unique values based on a single criterion, combine the SUM, IF, and COUNTIFS functions:

STEPS:

• Select the cell in which you want to count the unique values. Here, H5.
• Enter the formula.
`=SUM(IF(G5=\$D\$5:\$D\$13, 1/(COUNTIFS(\$D\$5:\$D\$13, G5, \$B\$5:\$B\$13, \$B\$5:\$B\$13)), 0))`
• Press Enter to see the result.

Formula Breakdown

`G5=\$D\$5:\$D\$13`: finds the cells containing Jhon. Here,Â G5.

`COUNTIFS(\$D\$5:\$D\$13, G5, \$B\$5:\$B\$13, \$B\$5:\$B\$13`: returns TRUE for addresses occurring once only; for repeated addresses. returns FALSE.

`1/(COUNTIFS(\$D\$5:\$D\$13, G5, \$B\$5:\$B\$13, \$B\$5:\$B\$13)`: divides 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)`: checks whether the conditions in the formula are met and 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))`: Â counts the total unique values.

### Example 2 –Â  Multiple Criteria to Count Unique Excel Values

The criteria are Customer Name andÂ Brands. The COUNTIFS function will count products whose customer names and brands are the same.

STEPS:

• Select the cell in which you want the result. Here, I5.
• Enter the formula.
`=COUNTIFS(D5:D13,G5,C5:C13,H5)`
• Press Enter.

D5:D13 indicates the Customer Name, and the criteria for this range is G5 (Jhon).

C5:C13 indicates the Brand, and the criteria for this range is H5 (Asus).

Read More: How to Count Unique Names in Excel

### Example 3 – Counting Different Number of Text Values in Excel

To count different numbers of unique text values, combine the SUM, ISTEXT, and COUNTIFS function. Here, the criteria are the text values of the Contact Address column.

STEPS:

• Select the cell in which you want to count the unique values using the criteria. Here, G5.
• Enter the formula.
`=SUM(--(ISTEXT(E5:E13)*COUNTIFS(E5:E13,E5:E13)=1))`
• Press Enter.
• There are 2 unique text values.

Formula Breakdown

`ISTEXT(E5:E13)`: returns TRUE for all addresses that are text values. FALSE, otherwise.

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

`ISTEXT(E5:E13)*COUNTIFS(E5:E13,E5:E13)`: multiplies the two formulas and returns 1 if they are met; returns 0 otherwise.

`SUM(--(ISTEXT(E5:E13)*COUNTIFS(E5:E13,E5:E13)=1))`: returns the unique text values.

### Example 4 – Counting Different Numeric Values

The SUM, ISNUMBER, and COUNTIFS functions can be combined to count unique numeric values.

STEPS:

• Choose the cell in which you want to count the unique values based on the numerical value as the criteria. Here, G5.
• Enter the formula.
`=SUM(--(ISNUMBER(E5:E13)*COUNTIFS(E5:E13,E5:E13)=1))`
• Press Enter.

Formula Breakdown

`ISNUMBER(E5:E13)`: For all the addresses that are numeric values, returns TRUE; FALSE otherwise.

`COUNTIFS(E5:E13,E5:E13)`: For all addresses that show just once, returns TRUE;FALSE otherwise.

`ISNUMBER(E5:E13)*COUNTIFS(E5:E13,E5:E13)`: multiplies the ISNUMBER formula and COUNTIFS formula. It returns 1 if criteria are met; 0 otherwise.

`SUM(--(ISTEXT(E5:E13)*COUNTIFS(E5:E13,E5:E13)=1))`: returns the unique number values.

<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF