Are you tired of searching for an easy and efficient way to conditionally count unique values in a range? Look no further! Google Sheets now offers the COUNTUNIQUEIFS function, designed to simplify this process and make your data analysis a breeze.
The All-in-One Solution for Conditional Counting
In the past, achieving a conditional count of unique values in Google Sheets required combining the COUNTUNIQUE function with the Filter function. But with the introduction of the COUNTUNIQUEIFS function, you can now accomplish this task in a single step.
Let’s dive into the world of the COUNTUNIQUEIFS function and explore its endless possibilities.
Unveiling the COUNTUNIQUEIFS Syntax
The COUNTUNIQUEIFS function in Google Sheets follows a simple syntax:
COUNTUNIQUEIFS(range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])Allow me to break down the function arguments for you:
- Range: The range or array from which you want to count the unique values.
- Criteria_range1: The range or array over which you want to evaluate the first criterion.
- Criterion1: The pattern or test you want to apply to criteria_range1.
- Criteria_range2 (optional): Additional ranges or arrays for evaluating subsequent criteria.
- Criterion2 (optional): Additional patterns or tests for the corresponding criteria_range2.
While these arguments may seem confusing at first glance, rest assured that I’ll guide you through each step in the formula section below.
Formula Examples: Unleashing the Power of COUNTUNIQUEIFS
Let’s begin with a simple example to help you grasp the usage of this new count function.
Example: Imagine you have recorded the scores of three participants in a long jump event. Each participant attempted three times, resulting in multiple scores. To find the count of unique scores for each participant, you can utilize the Countuniqueifs formula in Google Sheets.
Formula (in cell F2):
=COUNTUNIQUEIFS(B2:B, A2:A, E2)In this formula, B2:B represents the range, A2:A represents criteria_range1, and E2 contains the criterion1.
As you can see, when the criterion1 is set as “John” in cell E2, the formula returns a count of 2. This count corresponds to the unique scores achieved by John.
By analyzing the scores (8.2 m, 8 m, and 8.2 m), we can see that John has achieved two different scores, hence the count of 2.
Now that you’ve grasped the basic arguments of the Countuniqueifs function, let’s explore some advanced techniques.
Alternative Formula for Conditional Unique Value Counting
Before the introduction of COUNTUNIQUEIFS, I utilized a combination of COUNTUNIQUE and FILTER to achieve conditional unique value counting. Although COUNTUNIQUEIFS is a game-changer, it’s worth knowing the alternative combo formula:
=COUNTUNIQUE(FILTER(B2:B, A2:A = E2))Incorporating Multiple Criteria in COUNTUNIQUEIFS
Counting unique values with multiple criteria is a powerful feature offered by the Countuniqeifs function in Google Sheets. Whether you need to combine comparison operators or apply multiple criteria from different columns, this function has got you covered.
Criteria from Different Columns
When dealing with multiple criteria from different columns, employ the optional arguments ‘criteria_range2’ and ‘criterion2’. The following formula showcases the use of a comparison operator in conjunction with one of the criteria:
=COUNTUNIQUEIFS(A2:A, B2:B, ">100", C2:C, "Delivered")
The above formula counts the unique fruits when the ‘Order Qty’ is greater than 100 and the ‘Delivery Status’ is marked as “Delivered”.
Handling Date Criteria
If you’re working with date criteria, ensure that you structure the formula appropriately. For instance, a date criterion should be entered as follows:
">" & DATE(2019, 8, 14)Here’s an example formula to demonstrate the usage of date criteria in the conditional count unique formula:
=COUNTUNIQUEIFS(A2:A, D2:D, ">" & DATE(2019, 8, 14))Feel free to replace the comparison operator “>” with “=”, “<“, or any other suitable operator depending on your requirements.
Multiple Criteria from the Same Column
In certain scenarios, you may find it necessary to count unique values based on multiple criteria from the same column. Unfortunately, the Countuniqueifs function only supports one criterion per column, according to official Google Docs documentation. But fear not! I have a workaround that utilizes Regexmatch to overcome this limitation.
Here’s the formula and its explanation:
=ArrayFormula(COUNTUNIQUEIFS(B2:B, REGEXMATCH(A2:A, "John|Andie"), TRUE))
Formula Explanation:
The REGEXMATCH formula returns TRUE whenever the values in the row (Column A) match “John” or “Andie”, and FALSE otherwise. In the formula, B2:B represents the range, the output of the REGEXMATCH formula (TRUE/FALSE) serves as criteria_range1, and TRUE acts as the criterion.
Although we still only employ one criterion (the Boolean value TRUE), this workaround allows us to apply multiple criteria effectively.
If you have the two criteria stored in separate cells (e.g., cell E2 contains “John” and cell E3 contains “Andie”), simply modify the formula as follows:
=ArrayFormula(COUNTUNIQUEIFS(B2:B, REGEXMATCH(A2:A, E2 & "|" & E3), TRUE))And there you have it! You’re now equipped with the knowledge to master the COUNTUNIQUEIFS function in Google Sheets. Enjoy your newfound data analysis prowess!
A big thank you to Ryan for sharing this valuable tip regarding the introduction of this ‘new’ function. For more exciting Google Sheets tips and tricks, visit Crawlan.com
