How to Use Count Unique IF Function in Google Sheets
The COUNTUNIQUEIF function does not exist in Google Sheets. However, you can achieve the same results by combining the COUNTUNIQUE and FILTER functions. This combination allows you to count unique values based on specific criteria.
Here's how to use the COUNTUNIQUE and FILTER functions to count unique values based on specific criteria:
- Open your Google Sheet or create a new one.
- Prepare your data in a table format, with headers for each column.
Example
Let's say we have the following dataset:
| Order ID | Customer ID | Product | Quantity |
|---|---|---|---|
| 1 | 1001 | Apples | 10 |
| 2 | 1002 | Oranges | 15 |
| 3 | 1001 | Bananas | 20 |
| 4 | 1003 | Apples | 5 |
| 5 | 1001 | Apples | 10 |
| 6 | 1002 | Oranges | 10 |
| 7 | 1003 | Bananas | 5 |
In this example, we want to count the unique customers who purchased "Apples". The formula will be as follows:
=COUNTUNIQUE(FILTER(B2:B8, C2:C8 = "Apples"))Here's a breakdown of the formula:
B2:B8is the range of Customer IDs.C2:C8is the range of Products."Apples"is the criteria we want to filter by.
- Click on an empty cell where you want to display the result.
- Enter the formula mentioned above and press Enter.
- The result will be displayed in the selected cell. In this example, the result is
2, since there are 2 unique customers who purchased "Apples" (Customer IDs 1001 and 1003).
You can modify the formula according to your dataset and criteria. This method allows you to count unique values based on specific conditions in Google Sheets.
Did you find this useful?