How to Count Unique Rows Based on Two Criteria in Excel

Sometimes, we may want to count the number of rows in a table that meet certain criteria, such as having a specific value in one or more columns. For example, we may want to count how many students in each class passed the exam, or how many products in each category were sold in a month.

One way to do this is to use the COUNTIFS function, which counts the number of cells that match multiple criteria. The syntax of the COUNTIFS function is:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

where:

• criteria_range1 is the first range of cells to evaluate.
• criteria1 is the first condition to apply to the cells in criteria_range1.
• criteria_range2, criteria2, etc. are optional additional ranges and conditions to apply.

For example, if we have a table of student scores in cells A2:D11, where column A is the class name, column B is the student name, column C is the score, and column D is the pass/fail status, we can use the following formula to count how many students in class A passed the exam:

=COUNTIFS(A2:A11, "A", D2:D11, "Pass")

This formula will return 3, as there are three rows in the table that have “A” in column A and “Pass” in column D.

However, what if we want to count the number of rows based on two values, but also enforce uniqueness on a third value? For example, what if we want to count how many different students in each class passed the exam, ignoring duplicate names? In this case, the COUNTIFS function alone is not enough, as it will count the same student name multiple times if it appears in more than one row.

To solve this problem, we need to use another function, called SUMPRODUCT, which multiplies corresponding elements in two or more arrays and returns the sum of the products. The syntax of the SUMPRODUCT function is:

SUMPRODUCT(array1, [array2], ...)

where:

• array1 is the first array or range of values to multiply.
• array2, etc. are optional additional arrays or ranges of values to multiply.

For example, if we have two arrays of numbers in cells A2:A5 and B2:B5, we can use the following formula to calculate the sum of the products of the corresponding elements:

=SUMPRODUCT(A2:A5, B2:B5)

This formula will return 70, as it performs the following calculation:

(A2*B2) + (A3*B3) + (A4*B4) + (A5*B5) = (2*5) + (3*4) + (4*3) + (5*2) = 70

The SUMPRODUCT function can also handle arrays of logical values, such as TRUE or FALSE, by converting them to 1 or 0 respectively. This allows us to use the SUMPRODUCT function to perform logical operations on multiple arrays and return the sum of the results.

For example, if we have two arrays of logical values in cells A2:A5 and B2:B5, we can use the following formula to count how many pairs of values are both TRUE:

=SUMPRODUCT(--(A2:A5), --(B2:B5))

This formula will return 2, as it performs the following calculation:

(--(A2)*--(B2)) + (--(A3)*--(B3)) + (--(A4)*--(B4)) + (--(A5)*--(B5)) = (1*1) + (0*0) + (1*0) + (0*1) = 1 + 0 + 0 + 0 = 1

The double minus signs (--) are used to convert the logical values to numbers, as the SUMPRODUCT function cannot handle logical values directly.

By combining the SUMPRODUCT and COUNTIFS functions, we can create a formula that counts the number of rows grouped by two values and enforcing uniqueness on a third value. The general formula is:

=SUMPRODUCT((criteria_range1=criteria1)*(criteria_range2=criteria2)/COUNTIFS(criteria_range1, criteria_range1, criteria_range2, criteria_range2, unique_range, unique_range))

where:

• criteria_range1 and criteria_range2 are the ranges of cells that contain the two values to group by.
• criteria1 and criteria2 are the two values to match in the corresponding ranges.
• unique_range is the range of cells that contain the value to enforce uniqueness on.
• COUNTIFS(criteria_range1, criteria_range1, criteria_range2, criteria_range2, unique_range, unique_range) is an array formula that returns the number of times each combination of values in the three ranges appears in the table.
• (criteria_range1=criteria1)*(criteria_range2=criteria2) is an array formula that returns 1 for the rows that match both criteria and 0 for the rows that do not.
• / is the division operator that divides the first array by the second array, element by element.
• SUMPRODUCT sums up the results of the division, which gives the final count of unique rows that match both criteria.

Procedures

To use the formula to get an aggregated count of rows grouped by two values and enforcing uniqueness on a third value in Excel, follow these steps:

1. Identify the ranges of cells that contain the two values to group by, the value to enforce uniqueness on, and the output cell where you want to display the result.
2. Enter the formula in the output cell, using the appropriate cell references and values for the criteria and the ranges.
3. Press Enter to confirm the formula and see the result.

Comprehensive explanation

To understand how the formula works, let’s look at an example with a detailed explanation.

Scenario

Suppose we have a table of sales data in cells A2:D21, where column A is the product name, column B is the category, column C is the date, and column D is the sales amount. We want to count how many different products in each category were sold in January 2024, ignoring duplicate product names.

Here is the table of sales data:

We want to display the result in cells F2:G5, where column F is the category and column G is the count of unique products sold in January 2024.

Here is the output table:

Calculation

To calculate the count of unique products sold in January 2024 for category X, we can use the following formula in cell G2:

=SUMPRODUCT((B2:B21="X")*(MONTH(C2:C21)=1)/COUNTIFS(B2:B21, B2:B21, MONTH(C2:C21), MONTH(C2:C21), A2:A21, A2:A21))

Let’s break down the formula and see how it works.

First, we have the criteria range 1, which is B2:B21, the range of cells that contain the category values. We have the criteria 1, which is “X”, the value to match in the category column.

Next, we have the criteria range 2, which is C2:C21, the range of cells that contain the date values. We have the criteria 2, which is 1, the value to match in the date column. However, since the date values are formatted as dates, we need to use the MONTH function to extract the month number from them. The syntax of the MONTH function is:

MONTH(serial_number)

where:

• serial_number is a date value or a reference to a cell that contains a date value.

For example, if we have a date value of 01/01/2024 in cell A2, we can use the following formula to get the month number:

=MONTH(A2)

This formula will return 1, as January is the first month of the year.

Therefore, we use the MONTH function to convert the criteria range 2 (C2:C21) to an array of month numbers, and compare it with the criteria 2 (1).

Next, we have the unique range, which is A2:A21, the range of cells that contain the product names. This is the value that we want to enforce uniqueness on, meaning that we only want to count each product name once, even if it appears in more than one row.

To count the number of rows grouped by two values and enforcing uniqueness on a third value, we use the following formula:

=SUMPRODUCT((B2:B21="X")*(MONTH(C2:C21)=1)/COUNTIFS(B2:B21, B2:B21, MONTH(C2:C21), MONTH(C2:C21), A2:A21, A2:A21))

Let’s see how this formula works step by step.

First, we have the part that matches the two criteria:

(B2:B21="X")*(MONTH(C2:C21)=1)

This part returns an array of 1s and 0s, where 1 indicates that the row matches both criteria (category X and month 1) and 0 indicates that the row does not match both criteria. For example, the first row (A2:D2) matches both criteria, so it returns 1. The second row (A3:D3) does not match both criteria, so it returns 0. The result of this part is:

{1;0;0;0;0;0;1;0;0;1;0;0;1;0;0;1;0;0;1;0}

Next, we have the part that counts the number of times each combination of values in the three ranges appears in the table:

COUNTIFS(B2:B21, B2:B21, MONTH(C2:C21), MONTH(C2:C21), A2:A21, A2:A21)

This part returns an array of numbers, where each number represents the frequency of the corresponding row in the table. For example, the first row (A2:D2) has the combination of values (X, 1, A), which appears twice in the table, so it returns 2. The second row (A3:D3) has the combination of values (X, 1, B), which appears once in the table, so it returns 1. The result of this part is:

{2;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1}

Then, we have the division operator that divides the first array by the second array, element by element:

/

This part returns an array of numbers, where each number represents the fraction of the corresponding row in the table. For example, the first row (A2:D2) has a fraction of 1/2, meaning that it accounts for half of the rows that have the combination of values (X, 1, A). The second row (A3:D3) has a fraction of 1/1, meaning that it accounts for all of the rows that have the combination of values (X, 1, B). The result of this part is:

{0.5;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1}

Finally, we have the SUMPRODUCT function that sums up the results of the division, which gives the final count of unique rows that match both criteria:

=SUMPRODUCT(...)

This part returns a single number, which is the sum of the elements in the array. For example, the sum of the array is:

0.5 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 = 10.5

However, since we want to count the number of unique products, we need to round this number down to the nearest integer, as we cannot have half a product. Therefore, the final result is:

=ROUNDDOWN(10.5, 0)

This part returns 10, which is the count of unique products sold in January 2024 for category X.