The COUNTUNIQUEIFS function counts the unique values that meet multiple criteria in Google Sheets. This function accepts a variable number of arguments, each consisting of a range of cells and corresponding criteria.
It is similar to the COUNTIFS function, which also accepts multiple criteria. However, COUNTUNIQUEIFS counts unique values, whereas COUNTIFS counts all values that meet the specified criteria.
- 1 Syntax
- 2 Insert Math Symbols (Add-On)
- 3 Similar Functions
- 4 Examples
- 5 Video Tutorial
- 6 Related Tutorials
=COUNTUNIQUEIFS(range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])
range – The cells with the unique values
criteria_range – The cells to evaluate against the
criterion – The condition to apply to the
Insert Math Symbols (Add-On)
COUNT – Count the numeric values in a data set
COUNTA – Count the non-blank values in a data set
COUNTIF – Count the cells that match a criterion
COUNTIFS – Count the cells that match multiple criteria
COUNTUNIQUE – Count the unique values
COUNTUNIQUEIFS – Count the unique values that meet multiple criteria
COUNTBLANK – Count the blank cells
Example 1 – Conditionally Counting Unique Values With One Criterion
Firstly, let’s start with an example of counting unique values based on one condition.
The first argument,
A2:A13, specifies the
range of cells to count. The second and third arguments,
"East" are the
criteria_range containing the data to check against the
In other words, the COUNTUNIQUEIFS function counts the number of unique
items in the
Let’s look at another example using one criterion from a different angle.
Example 2 – Conditionally Counting Unique Values With One Criterion
Let’s keep the same source data but look for the number of
wrenches. This example will help us to understand how to build different formulas to analyze the same data.
To find how many warehouses are stocking
Wrenches, we will switch the values of the
criteria_range from the first example. We want to count unique values in the
B2:B13 if they match the
"Wrench" in the
In other words, how many
wrenches in stock?
Last but not least, let’s look at a more complex example.
Example 3 – Conditionally Counting Unique Values With Two Criteria
Lastly, we will look for unique values based on the satisfaction of two conditions.
As we increase the criteria, we start to see the power of the COUNTUNIQUEIFS function. In this example, we are looking for locations that have more than
12 cases of
pliers. We count the
warehouses by specifying
B2:B13 as the
range, then using
C2:C13 as the
As an interesting twist, since there was only one situation with more than
12 cases of
Pliers, we would get the same result using the COUNTIFS function.
Live Example in Google Sheets
Make a copy of this spreadsheet to see the examples in the live web app.
Three techniques to count unique values based on specified criteria.