How To Use SUMPRODUCT Function in Excel

The SUMPRODUCT function in Excel is useful when you need to return the sum of the products of corresponding ranges or arrays.

The function first multiplies then adds the values found in the input arrays. This may sound simple, but the SUMPRODUCT function can help make array formulas more elegant.

The rules for using the SUMPRODUCT function in Excel are as follows:

  • The function requires one or more ranges or arrays
  • The function then outputs the sum of the products of the corresponding ranges or arrays.

Let’s begin with a quick use-case where we can use the SUMPRODUCT function.

Suppose we have a spreadsheet that keeps track of items sold in a store. Each row has the item name, the item’s unit cost, and the total quantity sold. How do we get the total revenue of the store?

We can create a new column in the table that multiplies the unit cost of the item and the total quantity sold. Afterward, we can get the sum of that new column to get the total revenue.

The SUMPRODUCT function, however, makes it even simpler. We just have to enter the unit cost ranges and quantity sold into the formula. The function will automatically multiply each unit cost and quantity pair and add up the products to return a final count. This final count is equivalent to revenue.

This particular use case is just one way to apply the SUMPRODUCT function in Excel. The SUMPRODUCT can be combined with various other functions to create elegant solutions to calculating data.

Now that we know when to perform the SUMPRODUCT function, let’s take a look at how we can use the formula itself.

 

 

The Anatomy of the SUMPRODUCT Function

The syntax of the SUMPRODUCT function is as follows:

=SUMPRODUCT(array1, [array2], [array3], ...)

Let’s dissect this formula and understand what each of these terms means:

  • = the equal sign is how we start any formula in Microsoft Excel.
  • SUMPRODUCT() is our SUMPRODUCT function. It computes the sum of the products of given arrays. 
  • array1 refers to the first array argument whose components you want to multiply and later add together.
  • [array 2] and [array3] refer to the additional array arguments that you want to multiply and add.
  • Excel allows you to have up to 255 arrays as an argument for the SUMPRODUCT function.
  • The SUMPRODUCT function considers non-numeric array entries as zeros.
  • All array arguments must have the same dimension. Differing dimensions will return a #VALUE! error.

 

 

A Real Example of Using SUMPRODUCT Function

Let’s take a look at a real example of the SUMPRODUCT function being used in an Excel spreadsheet.

The example below shows a list of items. Each item has a corresponding cost per unit and quantity sold. Using the SUMPRODUCT function, we can easily compute the total revenue of all sales combined.

Using SUMPRODUCT function in Excel

 

To get the total sales, we just need to use the following formula:

=SUMPRODUCT(B2:B6,C2:C6)

We can also use the SUMPRODUCT and add criteria to consider when evaluating the formula. In the example below, we used the SUMPRODUCT function to get total revenue for mousepads only.

Using SUMPRODUCT function in Excel with filters

 

To get the total sales for mousepads only, we just need to use the following formula:

=SUMPRODUCT((A2:A6="Mouse Pad")*B2:B6*C2:C6)

The above formula might be difficult to parse. Why exactly are we multiplying a Boolean value with our original two ranges? 

Each value in A2:A6 is converted into a TRUE or FALSE value. Values equal to “Mouse Pad” will return TRUE, and the rest will return a FALSE value. Excel treats these Boolean values as either a 1 or 0 when multiplying with another array. This gives us the range {0;0;0;1;0} multiplied by the unit cost and quantity sold. 

Since any value multiplied by 0 is 0, only the values in the same row as “Mouse Pad” will return a non-zero value. In the end, the formula will return just the Mouse Pad sales.

We can also use the SUMPRODUCT to perform OR logic calculations. The example below adds another total called Keyboard and Mouse Sales. This value corresponds to the total number of keyboard and mouse sales. 

using OR logic with SUMPRODUCT function in Excel

 

To get this total, we can use the following formula:

=SUMPRODUCT((A2:A6="Mouse") + (A2:A6="Keyboard"),B2:B6,C2:C6)

The plus sign acts as the OR operator to return TRUE if any conditions apply. The resulting array should look like this: {1;1;0;0;0}. Multiplying that with the other ranges should cancel out the orders that are not for a mouse or keyboard.

You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to try out the SUMPRODUCT function in Excel, let’s begin writing it ourselves!

 

 

How to Use SUMPRODUCT Function in Excel

This section will guide you through each step needed to use the SUMPRODUCT function in Excel. You’ll learn how we can use this SUMPRODUCT function to return the sum of products of one or more ranges.

Follow these steps to start using the SUMPRODUCT function:

  1. First, we must select the cell where we will place our SUMPRODUCT function. In this example, we’ll use cell B8 to hold our final result.
    select a cell to start

  2. Next, we just simply type the equal sign ‘=‘ to begin the function, followed by ‘SUMPRODUCT(‘.
    type SUMPRODUCT into the formula bar

  3. Our first argument will be the first array in our calculation. We’ve selected the range B2:B6, which is our cost per unit.
    select first range as an argument

  4. Next, we’ll place our second argument for the SUMPRODUCT function. In this guide, we’ve selected the range C2:C6, which holds our values for quantity sold per item.
    select second range for SUMPRODUCT

  5. Hit the Enter key to return the final product. We now know that the total sales of the given table is $516.75.
    Final result of SUMPRODUCT function in Excel

We can also add criteria to the arguments of the SUMPRODUCT function. For example, we can add a criteria that the item must be a “Mouse” or a “Keyboard”

Follow this guide to find out how to do this with the SUMPRODUCT function:

  1. Instead of having only two arguments, we’ll need three. The new argument will be a Boolean statement: A2:A6=”Mouse Pad”.
    adding filters in SUMPRODUCT

  2. We can add the next two columns as separate arguments, or we can simply use the “*” symbol.
    You can multiply ranges inside SUMPRODUCT

  3. Hit the Enter key on your keyboard to evaluate the formula. In this case, we find out that a total of $38 was made from Mouse Pad sales alone.
    using SUMPRODUCT function in Excel with filters

That’s all you need to remember to start using the SUMPRODUCT function in Excel. This step-by-step guide shows how you can use the function to return the sum of products of one or more ranges in a spreadsheet.

The SUMPRODUCT function is just one example of a mathematical function you can use in Excel. With so many other Excel functions out there, you can surely find one that suits your use case.

Are you interested in learning more about what Excel can do? Stay notified of new guides like this by subscribing to our newsletter

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like