How to Use the Excel SUMPRODUCT Function

When it comes to knowing applications for jobs these days, Microsoft products still dominate. Microsoft Excel is probably the most complex of these products, but it is also the one that can do the most. Learning how to use Excel can make you a better candidate for jobs and a better employee at those jobs.

Both the PC and Mac Excel products are in use in offices around the world. Some of the most widely used versions are Excel 2007, Excel 2010, Excel 2013, the current version, and Excel 365. Excel, at its essence, is a spreadsheet tool to hold data and analyze it. It is the main way to store, look at, and analyze data. You can start to learn shortcuts, data validation, and text functions to get really great at working with your data. You can explore pivot tables and bar graphs to make your data look great for an audience. Get to know these Excel skills and get ready to be an Excel expert!

Let’s say you have mastered the basics of entering, organizing, and reading data in Excel. The real secret to becoming an expert is learning to use Excel formulas and functions.

What are functions and formulas?

I recommend learning a few basic Microsoft Excel functions, formulas, and tricks to make your life easier. Once you know how to use a formula, you can start using popular tools like VLOOKUP, INDEX, MATCH, and more that make your work faster and better. To figure out what else you need to know, check out this article on the most important advanced Excel skills for professionals.

Formulas and functions in Excel are great ways to get the most out of the product. People use the terms somewhat interchangeably. Technically, a function is a tool built into Excel you call by name, like VLOOKUP or SUMPRODUCT. A formula is what you write yourself and can include more than one function. A function is the name of a tool, and the formula will be what you build with it and how you use it.

Since they are part of Excel, each function has a proper format or syntax for its formula. The syntax is the correct way to use the function and enter the inputs. For example, the SUM function syntax calls a range of cells or a set of numbers. To use a function like SUMPRODUCT, you need to understand its syntax and parts. Excel SUMPRODUCT is a combination of a sum and a product.

What are sum and product?

There are two pieces to SUMPRODUCT, SUM, and PRODUCT. These are both math terms and Excel functions.

Sum is the total amount of numbers added together. In Excel, you can take a range of data and use a function that returns the sum. So, for example, if you have an inventory spreadsheet with apples, bananas, and cantaloupes on it. You have the quantity of each listed, and then you want to know how much fruit you have in total. You can calculate the sum of all three quantities by selecting the entire range.

In our example, let’s say you have 20 apples, 30 bananas, and 40 cantaloupes. You can sum that range of numbers and get 90.

Product is what you get when you multiply two numbers. So if bananas cost $0.50 each, and you know you sold 5, you can ask Excel to multiply that for you and get $2.50.

Finding and using an Excel function

Okay, so now that you feel excited about functions and know a little, how do you learn all these functions? The good news is, you do not need to memorize them. While a function like SUM is easy to remember the name and use of, some can be much harder, and there are tons of them. So there are two ways you can use the functions in Microsoft Excel. The first situation is if you know the function name and how the function works. Then you can type the name of the function and requirements directly into the cell where you want the answer to appear. This is what we just did with SUM.

If you do not know the exact name or requirements of a function or are not sure if there is a function for what you want, you can select the function from the list. Once you have selected it, a formula wizard will help you complete it as a formula. To access all the Excel functions, you need to select the Formulas tab on the ribbon at the top of your screen.

The SUMPRODUCT function is part of the Math and Trigonometry menu. Select the “Formulas” tab in the top menu in Excel with your spreadsheet open. Then select “Math & Trig.” A menu will drop down that is alphabetical.

Click on the “SUMPRODUCT” formula. This will launch the formula Builder on the right.

Why do you need SUMPRODUCT?

At first glance, the Excel SUMPRODUCT function 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 correctly — which you will learn in this tutorial — the SUMPRODUCT can be a powerful tool. This is especially true when working with a lot of data with more than one selection criteria.

In this article, we will understand how the SUMPRODUCT function works, its basic syntax, and take a look at some of its actual applications.

Understanding the SUMPRODUCT function

The Excel SUMPRODUCT function has a very simple syntax if you want to enter it yourself:

=SUMPRODUCT (array1, array2, array3)

Or,

=SUMPRODUCT (array1*array2*array3)

Entering arrays into your SUMPRODUCT formula

The Excel SUMPRODUCT function takes in two or more array arguments. An array is a range of cells, or a list of them. You can select them by clicking on the range of cells you want and using the function builder. Each array you put in will be the range of cells.

Let’s consider an example.

Suppose we have a list that includes the number of different types of fruits and vegetables (column A) and the price of each fruit or vegetable (column B):

What if we want to calculate the total value of all fruits and vegetables the old-fashioned way? We would have to multiply each item in column A with its corresponding data in column B and then add the totals. That is a long, difficult process, especially if you are working with long lists of data.

Thankfully, we can turn to the Excel SUMPRODUCT function.

To get the result of A1*B1 + A2 * B2 + A3* B3, we can simply write out a SUMPRODUCT formula in cell C2.

=SUMPRODUCT(A1:A3,B1:B3)

or

=SUMPRODUCT(A1:A3*B1:B3)

And the function returns the desired result. It is 330, in this case. SUMPRODUCT returns a number value and will treat non-numbers as zeros.

When to use the SUMPRODUCT function

So when would you use the Excel SUMPRODUCT function?

You would use the SUMPRODUCT function whenever you need the sum of the products of two columns. It is instrumental in cases where there is more than one operation you need to do within the same cell.

Practically, the most common applications of this function are:

• Calculating weighted average.
• Finding the sum of two or more lists.
• Calculations based on more than one criteria.

The following examples will help you understand the usage of Excel’s SUMPRODUCT function:

Case One: Calculating a weighted average

The first scenario where you might want to use a SUMPRODUCT function is a weighted average. This is when different scores have different values compared to each other, with some more important and worth more.

Let’s say that five people take part in a game. Each person performs five tasks. Each task has a different weight assigned for scoring parameters. These are negative one, one, two, two, and three, respectively.

We want to calculate the score for each contestant. We need the contestants’ scores in all five activities and the weight to decide the winner of the whole contest.

The best way to do this will be to use the Excel SUMPRODUCT function. With it, we can multiply individual scores in the tasks with corresponding weights. This lets us calculate the cumulative score for each player.

As we can see, the weightage remains common. So the first array is $B$2:$F$2 multiplied with the array B3:F3 (no $ placed), which changes with the players.

=SUMPRODUCT($B$2:$F$2, B3:F3)

The $ before the column letter and row number mean, hold that when we move to the next formula. That means now we can drag the function down through cell G7 to calculate the weighted total for each contestant.

As you can see, the highest cumulative score is 30 points for Alice. We were able to arrive at that number pretty quickly, thanks to the Excel SUMPRODUCT function.

Case Two: Calculating the sum of two lists

What if we want to calculate the sum of two lists? That is another great situation to use the Excel SUMPRODUCT function.

In this case, we have fruits and vegetables that have different prices and different quantities. The total price for the full amount of fruits and vegetables is the total inventory of each one times the total cost of each one. Our goal is to find the total value of the entire merchandise.

The solution is to multiply inventory with item price. This is something that the Excel SUMPRODUCT function will solve rather easily.

The formula is:

=SUMPRODUCT(B2:B11,C2:C11)

As we see in the formula, we are multiplying Column B (Inventory) Column C (Item Price). This then gets summed to give us the total value of all the inventory. In this case, it is $993.67.

Case Three: Calculations based on more than one criteria using the SUMPRODUCT function

Our second case works well as long as there is only one place that we store all these fruits and vegetables. But what if there are three places that we hold fruits and vegetables? Let’s take the above case and make it a bit more complex by adding another parameter, the warehouse that stores the fruits and vegetables.

There are three different warehouses where we store fruits and vegetables. We need to find the Total Value per Warehouse.

First of all, we calculate the solution for Warehouse Three. To do this, we will use the same formulas we used before. But this time, it will be with a small addition of criteria for selecting Warehouse Three. We do this by multiplying in our column with the warehouse info and checking if it equals what we want.

So, as seen above, we used the same formula but with this addition:

=SUMPRODUCT((B2:B11*C2:C11)*(E2:E11="Three")

Or in other words

=SUMPRODUCT((array1*array2)*(array3=”Criteria”)).

The criteria here will be Three. We can enter the string “Three” in quotes as we did or use a cell. If we make Cell A13 say Three, we can use that as our criteria. If we then bring in our dollar sign ($) to hold value, we can copy and place the same formula for all three, like this:

=SUMPRODUCT(($B$2:$B$11*$C$2:$C$11)*($E$2:$E$11=A13))

This will give us the exact data for warehouse two and warehouse one by replacing the criteria.

This way you can get all the information you need for each specific situation, all thanks to SUMPRODUCT.

Doing even more with SUMPRODUCT

If you want, you can add even further complexity to your calculations. For example, you can add in other criteria.

Let’s say we have rented motorbikes from various operators at different costs, which the operators charge per kilometer, or cost/KM. We aim to find the total cost incurred by renting bikes. For that, we would start with our basic calculation just like we did with our fruits and vegetables. The solution is to multiply the Cost/KM with KMs traveled.

Just like we did with fruits and vegetables, we would multiply our first array (cost / KM) by our second array, the number of kilometers. This would then return the sum that gives us the total cost.

Now let’s take our motorbike rental case and make it a bit more complex, like with our fruits and vegetables, by adding another parameter. In this case, let’s make it the operator that leases the motorbikes. There are three operators we hire bikes from, and we need to find the total cost per operator.

Finally, we can add another criterion to check besides that of operator, like Routes. Imagine that we want to calculate the total cost per operator for a particular route, say Route Two. For this calculation, we will keep the same formula with another criterion, Route Two.

To do this, we multiply one criterion with another. In this case, it is Operator Two with Route Two to know the total cost we need to pay to Operator Two for Route Two. Remember that we can use the string “Route 2” instead of a cell name. This illustrates the two ways of entering criteria in the Excel SUMPRODUCT function – string and cell.

Avoiding challenges

The more complicated your calculations get, the more potential areas there are for challenges. As I mentioned earlier, the SUMPRODUCT function can only use numbers unless it is for a criterion. That means it will treat anything that isn’t a number, or that it doesn’t realize is a number, as a zero.

The arrays that you enter have to all be the same size. The SUMPRODUCT function will return an error if they do not match. This will usually be a #VALUE error.

Be careful with these, and with setting up your formulas and criteria. If you do, you will soon feel like a pro getting the information you want out of spreadsheets!

What else can you do in Excel?

The Excel SUMPRODUCT function is not just great for not just calculating the sum of two lists. It is also wonderful for making complex selections and calculations based on more than one criteria. A function 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. There are little versatile functions and surprises like that throughout Microsoft Excel.