How to Use Excel SUMPRODUCT Function (Examples + Video)

Excel SUMPRODUCT Function (Example + Video)

When to use Excel SUMPRODUCT Function

SUMPRODUCT function can be used when you want to first multiply two or more sets to arrays and then get its sum.

What it Returns

It returns a number that represents the sum of the product of two or more arrays.

Syntax

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

Input Arguments

• array1 – The first array argument whose components you want to multiply and then add.
• [array 2] – (optional) Second array arguments whose components you want to multiply and then add. There can be a maximum of up to 255 arrays that can be used in SUMPRODUCT function.

Additional Notes

• The size of all the arrays must be the same.
• Non-numeric entries are treated as 0.

Excel SUMPRODUCT Function – Live Example

SUMPRODUCT With Multiple Criteria

One of my favorite uses of SUMPRODUCT is adding up numbers that meet two or more conditions at once. It even works in older Excel versions that don’t have SUMIFS.

Say I have sales data with the Region in column B, the Product in column C, and the Sale amount in column D (rows 2 to 11). I want the total sales where the Region is East AND the Product is Laptop.

Here’s the formula I’d use:

=SUMPRODUCT((B2:B11="East")*(C2:C11="Laptop")*D2:D11)

This returns 4200, the total of every sale that is both from the East and a Laptop.

Here’s why it works. Each condition like (B2:B11=”East”) returns an array of TRUE and FALSE values. When you multiply arrays, Excel reads TRUE as 1 and FALSE as 0.

So a row becomes 1 only when both conditions are true, and 0 when either one fails. Multiply that by column D and only the matching sales survive. SUMPRODUCT then adds them up.

Want an OR instead of an AND? Add the condition arrays rather than multiplying them, and wrap them in a >0 check so rows that match both aren’t counted twice:

=SUMPRODUCT(((B2:B11="East")+(C2:C11="Laptop")>0)*D2:D11)

This returns 8250, the total of every sale that is either from the East or a Laptop (or both).

Pro Tip: This SUMPRODUCT trick was the standard way to do multi-criteria sums before SUMIFS came along. On a modern Excel, =SUMIFS(D2:D11,B2:B11,”East”,C2:C11,”Laptop”) gives the same 4200 and reads easier. SUMPRODUCT still wins when you need math on the criteria themselves that SUMIFS can’t handle.

Excel SUMPRODUCT Function – Video Tutorial

Related Excel Functions:

• Excel AVERAGE Function.
• Excel AVERAGEIF Function.
• Excel AVERAGEIFS Function.
• Excel SUM Function.
• Excel SUMIF Function.
• Excel SUMIFS Function.

You May Also Like the Following Excel Tutorials:

• How to Calculate Weighted Average in Excel.