Excel SUMPRODUCT function

In Excel, the SUMPRODUCT function can be used to multiply two or more columns or arrays together, and then get the sum of products. In fact, the SUMPRODUCT is a useful function which can help to count or sum cell values with multiple criteria like the COUNTIFS or SUMIFS function. This article will introduce the function syntax and some examples for this SUMPRODUCT function.

• Example 1: Basic use of SUMPRODUCT function
• Example 2: Sum cells with multiple criteria with SUMPRODUCT function
• Example 3: Count cells with multiple criteria with SUMPRODUCT function
• Example 4: Calculate the weighted average with SUMPRODUCT function

 Syntax:

The syntax for the SUMPRODUCT function in Excel is:

 Arguments:

• array1: Required. The first array or range of cells you want to multiply and then add.
• array2: Optional. The second array or range of cells you want to multiply and then add.

Notes:

• 1. If there are non-numeric values in your range, the SUMPRODUCT treats them as zeros.
• 2. If the arrays or ranges have different number of rows and columns, the SUMPRODUCT function will return a #VALUE! error.
• 3. If there are logical tests in the arrays, they will create TRUE and FALSE values. In most cases, you'd need to convert them to 1 and 0 by using the double unary operator (--).
• 4. SUMPRODUCT function can handle up to 255 arguments in Excel 2007 and later versions, and only 30 arguments in earlier Excel versions.
• 5. SUMPRODUCT does not support wildcard characters.

 Return:

Return the result of multiplied and summed arrays.

 Examples:

Example 1: Basic use of SUMPRODUCT function

The basic use of the SUMPRODUCT is to multiply two columns and then add them up, for example, you have the following screenshot data, now you want to multiply the order and the unit price, and then sum all the multiply results together to get the total price.

To deal with this task, please apply the below formula:

Explanation: This formula SUMPRODUCT(B2:B7,C2:C7) = B2*C2+B3*C3+B4*C4+B5*C5+B6*C6+B7*C7.

And then, press Enter key, the cells in column B is multiplied by its corresponding cells in the same row of column C, and the results are added up. See screenshot:

Example 2: Sum cells with multiple criteria with SUMPRODUCT function

Except for the SUMIFS function to sum cell values based on multiple conditions, the SUMPRODUCT function also can solve this job quickly and easily.

Case 1: Sum cell values if contains specific text in another column

Supposing, you have a table of data as below screenshot shown, now, you want to calculate the total price which the product is KTE and the name is David, how could you do by using the SUMPRODUCT formula in Excel?

1. Please enter or copy any one of the below formulas into a blank cell:

• Tips: In the above formula:
• A2:A12="KTE": is the first criteria_range and criteria you want to sum based on.
• B2:B12="David": is the second criteria_range and criteria you want to sum based on.
• D2:D12: is the sum range that you want to sum the cell values.

2. Then, press Enter key to get the result as you need:

Case2: Sum cells with multiple criteria with OR logic

To sum cells based on multiple criteria with OR logic, you should use the plus symbol (+) to join the multiple conditions together within the SUMPRODUCT function.

For example, I want to sum the total price of the product KTE and KTO in column A as below screenshot shown:

Please apply the following formula into a blank cell:

And then, press Enter key, the total price of the product KTE and KTO have been calculated, see screenshot:

Case3: Sum cells with multiple criteria with OR and AND logic

In some situations, you may need to sum cells with both OR and AND logic at the same time. The SUMPRODUCE function also can solve this job with ease.

Asterisk (*) is used as the AND operator.

Plus symbol (+) is used as the OR operator.

Let’s see an example, for summing the total price which product is KTE and KTO when the sale is greater than 200.

You should copy or enter the below formula:

Then press Enter key to get the result as you need:

Example 3: Count cells with multiple criteria with SUMPRODUCT function

Generally, this SUMPRODUCT function also can help us to count the cells with multiple criteria in Excel. As same as the sum values, you just need to remove the sum_range argument from the formula.

Case1: Count cells with multiple criteria with AND logic

Here, I want to count the cells that product is KTE and the sale is greater than 200. So, please use any one of the below formulas:

• Tips: In the above formula:
• A2:A12="KTE": is the first criteria_range and criteria you want to count based on.
• B2:B12>200: is the second criteria_range and criteria you want to count based on.

And then, press Enter key to get the number of the result:

Case2: Count cells with multiple criteria with OR logic

For counting the number of products KTE and KTO in column A, please apply this formula:

And then, press Enter key to get the number of the product KTE and KTO. See screenshot:

Case3: Count cells with multiple criteria with OR and AND logic

To count the number of products KTE and KTO which sale is greater than 200, you should apply the following formula:

Then, press Enter key to get the number of result you need:

Example 4: Calculate the weighted average with SUMPRODUCT function

Normally, we can calculate the average of range of cells quickly and easily. But, if you want to calculate the average of a list of values with different levels of relevance, it means to calculate the weighted average. There is no direct function for getting it in Excel. But, the SUMPRODUCT function can help you to deal with it.

For example, I have a table of grade statistics for a student, each task takes the different weight as below screenshot shown.

To calculate the weighted average, please use the following formula:

Explanation: This formula: SUMPRODUCT(B2:B6, C2:C6) / SUM(C2:C6) = (B2*C2+B3*C3+B4*C4+B5*C5+B6*C6)/(C2+C3+C4+C5+C6)

And then, press Enter key, the weighted average has been calculated at once, see screenshot:

 More articles with SUMPRODUCT function: