How to Count Unique Values in Excel Based on Criteria

To count distinct values in one column based on name in another column, you need to use a formula that can filter out the duplicates and apply the criteria. There are different ways to do this, but one of the easiest methods is to use the UNIQUE and FILTER functions together. These are dynamic array functions that are available only in Excel 365.

The UNIQUE function returns an array of unique values from a range or an array. The FILTER function returns an array of values that meet one or more criteria. By combining these two functions, you can get an array of unique values that match the name in another column. Then, you can use the COUNTA function to count the number of elements in the array.

Procedures

To count distinct values in one column based on name in another column, follow these steps:

1. Select a cell where you want to enter the formula.
2. Type the following formula, replacing the range and criteria references with your own:

   =COUNTA(UNIQUE(FILTER(range, criteria_range = criteria,“”)))

3. Press Enter to confirm the formula. Excel will automatically spill the result to the selected cell.

Explanation

Let’s use an example to illustrate how the formula works. Suppose you have a table of sales data like this:

You want to count the number of distinct products sold by John. To do this, you can use the following formula in cell F2:

=COUNTA(UNIQUE(FILTER(B2:B10, A2:A10 = "John","")))

Here is how the formula works:

• The FILTER function takes the range B2:B10 as the first argument, which contains the product names. It also takes the criteria_range A2:A10 and the criteria “John” as the second argument. It returns an array of product names that are sold by John. The third argument is an empty string (“”), which is used as the default value if no values match the criteria. The result of the FILTER function is:

  {“A”;“C”;“B”;“A”;“C”}

• The UNIQUE function takes the array returned by the FILTER function as the argument and returns an array of unique values. It removes any duplicates from the array. The result of the UNIQUE function is:

  {“A”;“C”;“B”}

• The COUNTA function takes the array returned by the UNIQUE function as the argument and returns the number of elements in the array. It counts all non-empty values in the array. The result of the COUNTA function is:

  3

Therefore, the final result of the formula is 3, which means there are 3 distinct products sold by John.

Example

To demonstrate the formula with a scenario and an example, let’s use a different table of data. Suppose you have a table of student grades like this:

You want to count the number of distinct subjects that Alice has a grade of 80 or higher. To do this, you can use the following formula in cell F2:

=COUNTA(UNIQUE(FILTER(B2:B9, A2:A9 = "Alice", C2:C9 >= 80,"")))

Here is how the formula works:

• The FILTER function takes the range B2:B9 as the first argument, which contains the subject names. It also takes two criteria_ranges A2:A9 and C2:C9 and two criteria “Alice” and 80 as the second argument. It returns an array of subject names that are taken by Alice and have a grade of 80 or higher. The third argument is an empty string (“”), which is used as the default value if no values match the criteria. The result of the FILTER function is:

  {“Math”;“English”;“Science”}

• The UNIQUE function takes the array returned by the FILTER function as the argument and returns an array of unique values. It removes any duplicates from the array. The result of the UNIQUE function is:

  {“Math”;“English”;“Science”}

• The COUNTA function takes the array returned by the UNIQUE function as the argument and returns the number of elements in the array. It counts all non-empty values in the array. The result of the COUNTA function is:

  3

Therefore, the final result of the formula is 3, which means there are 3 distinct subjects that Alice has a grade of 80 or higher.

Excel table

To show the formula in an Excel table, you can use the following steps:

1. Select the range of data that you want to convert to a table. In this case, select A1:C9.
2. Go to the Insert tab and click on the Table command in the Tables group.
3. In the Create Table dialog box, check the option “My table has headers” and click OK.
4. Excel will create a table with the data and assign default names to the columns. You can rename the columns as you like by clicking on the header cells and typing the new names.
5. In the table, select the cell where you want to enter the formula. In this case, select F2.
6. Type the following formula, using the table column names instead of the cell references:

   =COUNTA(UNIQUE(FILTER([Subject], [Name] = “Alice”, [Grade] >= 80,“”)))

7. Press Enter to confirm the formula. Excel will automatically spill the result to the selected cell.

The formula will work the same way as before, but it will use the table column names instead of the cell references. This makes the formula easier to read and maintain. You can also use the table name in the formula, which is Table1 by default. For example, you can write the formula as:

=COUNTA(UNIQUE(FILTER(Table1[Subject], Table1[Name] = "Alice", Table1[Grade] >= 80,"")))

The result of the formula is still 3