Use SUMPRODUCT and COUNTIF together in Excel

SUMPRODUCT and COUNTIF are two powerful functions in Excel that can be used together in formulas to perform various calculations, for example, return the count of duplicate items between two lists.

The SUMPRODUCT function multiplies corresponding components in two or more arrays and returns the sum of those products. The COUNTIF function, on the other hand, counts the number of cells in a cell range that meet specific criteria.

This tutorial shows three examples of how to apply the SUMPRODUCT and COUNTIF functions together in Excel to perform various tasks.

How to Use SUMPRODUCT and COUNTIF Functions Together in Excel

Let’s look at the following three examples of using SUMPRODUCT and COUNTIF together in Excel:

1. Count duplicate items between two lists.
2. Count the number of cells with values within a specific range.
3. Count the number of cells with unique values in a cell range.

Example #1: Use a Combination of SUMPRODUCT and COUNTIF Functions to Count Duplicate Items Between Two Lists

Suppose we have received the following two lists of electronic gadgets from two online stores.

We want to use a formula combining the SUMPRODUCT and COUNTIF functions to compute the number of duplicate items between the two lists.

We use the steps below:

1. Name the two lists:

• Select the cell range A2:A10 and enter “Shop_A” on the Name Box. Note that the name should not have spaces.

• Select the cell range B2:B10 and enter “Shop_B” on the Name Box. The name should not have spaces.

2. Select cell D2 and type in the formula below:

3. Press Enter.

The formula returns the value 4, which is the number of duplicate items between the two lists.

Explanation of the formula

• COUNTIF(Shop_A,Shop_B) The COUNTIF function returns a one (1) if an item on the Shop_A list is also on the Shop_B list otherwise, it produces a zero (0).
• SUMPRODUCT(COUNTIF(Shop_A,Shop_B)) The SUMPRODUCT function sums the array of zeros and ones returned by the COUNTIF function.

Example #2: Combine the SUMPRODUCT and COUNTIF Functions to Count the Number of Cells Containing Values Within a Particular Range

We can use SUMPRODUCT and COUNTIF functions to determine the number of cells in a dataset containing values within a particular range.

Let’s consider the following dataset showing a list of employees’ salaries and their designation and branch.

Let’s consider the following dataset showing a list of employees’ salaries and their designation and branch.

We want to use the SUMPRODUCT and COUNTIF functions to calculate the number of employees earning between USD 1,400 and USD 2,000.

We use the below steps:

1. Select cell F2 and type in the formula below:

2. Press Enter.

The formula returns the value 7, the count of cells in the cell range D2:D15 containing values between 1,400 and 2,000.

Explanation of the formula

• SUMPRODUCT(COUNTIF(D2:D15,”>1400″)) This first part of the formula returns the value 10, which is the count of cells in the cell range D2:D15 with values greater than 1,400.
• SUMPRODUCT(COUNTIF(D2:D15,”>2000″)) This second part of the formula computes the number of cells in the cell range D2:D15 containing values greater than 2000, which is 3.
• The value 3 returned by the second part of the formula is subtracted from the number 10 produced by the first part of the formula, and the result is 7, the count of cells with values between 1,400 and 2,000.

Example #3: Combine the SUMPRODUCT and COUNTIF Functions to Count Unique Values in a Cell Range

We can use the SUMPRODUCT and COUNTIF functions to determine the number of unique values in a cell range.

Let’s consider the following dataset of names. First, notice that some names are repeated:

We want to use a formula that combines the SUMPRODUCT and COUNTIF functions to calculate the number of cells in the cell range A2:A10 with unique names.

We use the following steps:

1. Select cell C2 and type in the below formula:

2. Press Enter.

The formula returns the value 6, the number of unique names on the list.

Explanation of the formula

• COUNTIF(A2:A10,A2:A10) The COUNTIF function is configured to use the cell range A2:A10 as both the range and criteria arguments. The function returns an array of numbers representing the times each name appears on the list. In this example, the function returns the value 2 if a name appears twice and the value 1 if the name appears once.

For example, James appears twice, Peters appears twice, and so on.

• 1/COUNTIF(A2:A10,A2:A10) Each value in the array returned by the COUNITF function is used as a divisor, and the value 1 as the dividend (numerator). This division produces another array of ones and fractional values:

• Finally, the SUMPRODUCT function sums up the array of ones and fractional values to produce the number of unique names on the list:

Dealing With Blank Cells

Notice that if blank cells are in the cell range with names, the formula will return a #DIV/0! error instead of a number, as seen below:

We use the following steps to compute the number of unique names and exclude the blank cells:

1. Select cell C2 and type in the below formula:

2. Press Enter.

The new formula returns the value 6, the correct count of unique names on the list.

Explanation of the formula

• COUNTIF(A2:A11,A2:A11&””) We concatenate an empty string to the data in the criteria argument to prevent zeros from appearing in the array produced by the COUNTIF function. Otherwise, a zero in the divisor will cause the formula to produce the #DIV/0! error.
• (A2:A11<>””) This part of the formula ensures that blank cells are excluded from the final count by making the numerator zero for the relevant counts.
• Finally, the SUMPRODUCT function adds the values in the resultant array. The result is the count of unique names on the list.

Conclusion

SUMPRODUCT and COUNTIF are two powerful functions in Excel that we can use together in formulas to perform various calculations.

Combining these two powerful functions allows you to perform complex calculations in Microsoft Excel efficiently.

This tutorial gave the following three examples of using SUMPRODUCT and COUNTIF together in Excel:

• Count duplicate items between two lists.
• Count the number of cells with values within a specific range.
• Count unique values in a cell range.

We hope you found the tutorial helpful.