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:
- Countif A Specific Value Across Multiple Worksheets
- Supposing, I have multiple worksheets which contain the following data, and now, I want to get the number of occurrence of a specific value “Excel” from theses worksheets. How could I count specific values across multiple worksheets?
- Find And Get The Largest Value Based On Multiple Criteria In Excel
- In Excel, we can apply the max function to get the largest number as quickly as we can. But, sometimes, you may need to find the largest value based on some criteria, how could you deal with this task in Excel?
- Calculate Weighted Average In Excel
- For example, you have a shopping list with prices, weights, and amounts. You can easily calculate average price with the AVERAGE function in Excel. But what if weighted average price? In this article, I will introduce a method to calculate the weighted average, as well as a method to calculate weighted average if meeting specific criteria in Excel.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
