At first glance, the SUMPRODUCT formula might sound a little frivolous. After all, how many times do you really need to multiply two ranges and sum their total? But when used right – which you’ll learn in this tutorial – the SUMPRODUCT can be a powerful tool, especially when working with a lot of data with multiple selection criteria.
You can learn more about SUMPRODUCT and other similar formulas in this beginners course on Excel
In this tutorial, we will understand how the SUMPRODUCT formula works, its basic syntax, and take a look at some of its actual applications.
Understanding the SUMPRODUCT Formula
SUMPRODUCT has a very simple syntax:
Let’s consider an example.
Suppose we have a list that includes the number of different types of candies (column A) and the price of each candy (column B):
If we want to calculate the total value of all candy the old fashioned way, we would have to multiply each item in col A with its corresponding item in col B and then add the totals. That’s a long, laborious process, especially if you’re working with long lists of data.
Thankfully, we can turn to the SUMPRODUCT formula.
To get the result of A1*B1 + A2 * B2 + A3* B3, we can simply write the SUMPRODUCT formula in cell B4
=SUMPRODUCT(A1:A3,B1:B3) or = SUMPRODUCT(A1:A3*B1:B3)
And we get the desired result – 330, in this case.
Applications of SUMPRODUCT Formula
The SUMPRODUCT formula is particularly useful in cases where there are multiple multiplications and subsequent additions to be done with same cells.
Practically, the most common applications of this formula are:
Calculating weighted average.
Finding the sum of two or more lists.
Calculations based on multiple criteria.
The following examples will help you understand the usage of Excel’s SUMPRODUCT formula:
Case 1: Calculating Weighted Average
Suppose there are five people who take part in a game and perform 5 tasks where each task has different weightage assigned for scoring parameters, – 1, 1, 2, 2 & 3 respectively.
We want to calculate the score for each contestant based on his score in all 5 activities and decide upon the winner.
The best way to do this will be to use the SUMPRODUCT formula and multiply individual scores in the tasks with corresponding weights to calculate cumulative score for each player.
As we see, the weightage remains common, hence $C$1:$G$1 multiplied with C3:G3 (no $ placed) which changes with the players. We can now drag the formula down till cell H7.
As you can see, the highest cumulative score is 36 for player C, which we arrived at pretty easily thanks the SUMPRODUCT formula.
Already familiar with Excel? Why not take this advanced course on Excel to dive deeper into Excel functions and formulas?
Case 2: Calculating the Sum of Two Lists
In this case, we have bikes taken from various operators at different costs which are charged at Cost / KM. Our aim is to find the total cost incurred.
The solution is to multiply Cost/ KM with KMs travelled – something the SUMPRODUCT formula will solve rather easily.
As we see in the formula, List 1 (cost / KM) is being multiplied with List 2 – No. of KMs which thereafter gets summed to give us the total cost.
Completely new to Excel? Then try out this Excel basic course to get started!
Case 3: Calculations Based on Multiple Criteria Using SUMPRODUCT Formula
Let’s take the above case and make it a bit more complex by adding another parameter, the Operator that leases the motorbikes.
There are 3 Operators from where the bikes are hired and we need to find the Total Cost / Operator.
First of all we calculate the solution for Operator 3, for which we will use the same formulas used previously but with a small addition of a criteria for selecting Operator 3.
Hence, as seen above, we used the formula =SUMPRODUCT((list1*list2)*(List 3=”Criteria”)). The criteria here will be Operator 3, i.e. cell F14. Alternatively, we could also enter the string “Operator 3”.
If we want to find the same data for Operator 2, we only have to replace Cell F14 with Operator 2 instead of Operator 3.
To add even further complexity to the above case, we will add another criterion of Routes where we want to calculate the total cost per operator for a particular route, say Route 2.
For this calculation, we will be keep the same formula with another criterion of Route 2 in the formula as shown below.
As seen above, we multiply one criterion with another which is Operator 2 with Route 2 to know the total cost to be paid to Operator 2 for Route 2.
As you can see, we’ve used the string “Route 2” instead of cell name (as we did for Operator 2). This illustrates the two ways of entering criteria in the SUMPRODUCT formula – string and cell.
The SUMPRODUCT formula is great for not just calculating the sum of two lists, but also for making complex selections and calculations based on multiple criteria. A formula that, at first, looked like it would be of little more use than making rather obvious calculations turned out to be so much more helpful. Excel is filled with little surprises like that. You can learn more about similar Excel functions and formulas in this advanced course on Excel.