If you are working on different categories of information in an Excel worksheet, you might need to find out and count all the unique values of the categories based on the criteria set by one or more existing categories in other columns of the worksheet. In this tutorial, I will show you how to count unique values based on criteria in another column in Excel.
How to Count Unique Values Based on Criteria in Another Column in Excel: 4 Easy Ways
Let’s assume a scenario where we have an Excel file that contains information about the different kinds of fruits that a businessman exports to different countries in Europe. We have the Product name, and the Country to which the product is exported. We will use this Excel worksheet to count unique values in one column based on criteria in the other column of the Excel worksheet. The image below shows that we have found out and counted the number of all the unique types of fruits that the businessman sent to Canada.
Method 1: Combine the UNIQUE, LEN, and FILTER Functions to Count Unique Values Based on Criteria in Another Column
If you have access to Microsoft Office 365, the easiest way then will be to use the UNIQUE function exclusively in Excel 365 to count unique values based on criteria in another column.
Steps:
- Write down the following formula in cell F9.
=SUM(--(LEN(UNIQUE(FILTER(B5:B14,C5:C14=F8,"")))>0))
Formula Breakdown:
- The FILTER function will return those cells from the Product column where the corresponding cell values in the Country column are Canada (F8).
- The UNIQUE function will extract the unique values from the cell values returned by the FILTER
- The LEN function will check if the length of each unique item extracted by the UNIQUE function is greater than zero (0). It will return TRUE if the length of an item is greater than zero (0). Otherwise, it will return FALSE.
- We have inserted the double minus signs (—) to transform the TRUE and FALSE values returned by the LEN function to 1 and 0
- We will then sum up all the values of this list and return the total count of unique values.
- Upon pressing ENTER, we will get the total count of the types of fruits that were exported to Canada. The businessman exported fruits to Canada 4 times. But as Banana was exported twice to Canada, the formula counts cell B13 as duplicate. So, the cell returns 3 as the total count of unique types of fruits exported to Canada.
- We can also include multiple criteria from different columns to count unique values. For example, we have added a Month column to indicate on which month a specific product is exported to a country. We will use the Month column as the second criteria along with the Country column to count the unique types of fruits exported to Canada in June.
- We will enter the following formula in cell G10.
=SUM(--(LEN(UNIQUE(FILTER(B5:B14,(C5:C14=G8 )*( D5:D14=G9),"")))>0))
- Upon pressing ENTER, we will get the total count of the types of fruits that were exported to Canada in June. The businessman exported fruits to Canada 4 times. But as Banana was exported twice to Canada in June, the formula will exclude cells B8 and B13. So, the cell returns 2 as the total count of unique types of fruits exported to Canada in June.
Method 2: Count Unique Values Based on Criteria in Another Column Merging the UNIQUE, ROWS, and FILTER Functions
Alternatively, we can also use another formula consisting of the UNIQUE, ROWS, and FILTER function to count unique values based on criteria in another column. Let’s see how we can do that.
Steps:
- We will write down the following formula in cell G9.
=IFERROR(ROWS(UNIQUE(FILTER(B5:B14,C5:C14=G8))), 0)
Formula Breakdown:
- This formula is almost similar to the one we have written above for method 1. The difference is that we have used the ROWS function instead of the LEN and SUM functions to count the number of unique values.
- The ROWS function will return the number of rows or the unique cell values returned by the UNIQUE.
- Upon pressing ENTER, we will get the total count of the types of fruits that were exported to Canada.
Method 3: Use the COUNTA Function to Count Unique Values Based on Criteria in Another Column
We can use the COUNTA function in Excel to count unique values based on criteria in another column more conveniently. We have to do the following.
Steps:
- We will write down the following formula in cell G9.
=COUNTA(UNIQUE(FILTER(B5:B14,C5:C14=G8)))
Formula Breakdown:
- This formula is also similar to the one used in method 1. The COUNTA function will count all the unique cell values returned by the UNIQUE.
- Upon pressing ENTER, we will get the total count of the types of fruits that the businessman exported to Canada.
Method 4: Count Unique Values Based on Criteria in Another Column Using an Array Formula
If you are familiar with the array formula, you can then use one to count unique values based on criteria in another column in Excel. For example, we will count the number of unique countries the businessman exported Apples to. We will follow the below steps.
Steps:
- We will write down the following formula in cell G9.
=SUM(IF("Apple"B5:B14,1/(COUNTIFS(B5:B14,"Apple",$C$5:$C$14,$C$5:$C$14)),0))
Formula Breakdown:
- Upon pressing ENTER, we will get the total count of the countries the businessman exported apples to. The businessman exported apples to 4 unique countries.
- We can also use another array formula using the COUNTIF function to count unique values based on criteria in another column. For example, we will count the number of unique Order IDs of the orders sent to Canada. We will follow the below steps.
- We will write down the following formula in cell G9.
=SUM(IF(D5:D14=G8,1/COUNTIFS(B5:B14,B5:B14)))
- Upon pressing ENTER, we will get the unique Order IDs of the order the businessman sent to Canada. The businessman sent 4 orders to Canada but 3 of them have unique order ids while the other one shares the order id with one of those 3 unique ids. So, the formula returns 3 as the total count of unique order ids of the orders sent to Canada.
Quick Notes
- Formulas used in methods 4 and 5 are array formulas. So, you must press CTRL+SHIFT+ENTER together to insert the formula in a cell. It will put two curly braces around the whole formula.
- The UNIQUE function is an exclusive function currently available only for Excel 365. So, it will not work in your worksheet if you do not have Excel 365 on your PC.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
Conclusion
In this article, we have learned how to count unique values based on criteria in another column in Excel. I hope from now on you can count unique values based on criteria in another column in Excel easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!
<< Go Back to Count | Unique Values | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
