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.

##### Table of Contents

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.

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.

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.

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:

- 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.

- Next, we just simply type the equal sign ‘
**=**‘ to begin the function, followed by ‘**SUMPRODUCT(**‘.

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

- 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.

- Hit the
**Enter**key to return the final product. We now know that the total sales of the given table is $516.75.

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:

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

- We can add the next two columns as separate arguments, or we can simply use the “*” symbol.

- 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.

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!