Using Excel’s SUMPRODUCT Function

The SUMPRODUCT function is a powerful Excel tool that multiplies corresponding numbers in two or more arrays and then adds those products together.

Think of it as completing two operations—multiplication and addition—in one elegant step.

It’s especially useful when you need to perform conditional calculations or weighted analyses without creating messy helper columns or complicated array formulas.

SUMPRODUCT Function Details

Syntax

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

Key Parts:

Here’s how the SUMPRODUCT function works behind the scenes:

• Step 1: Multiply
  Each element in array1 is multiplied by the corresponding element in array2, array3, and so on.
• Step 2: Sum
  After all the multiplications are completed, Excel adds up the results to give a single final number.

It’s similar in name to the SUM Function, but is much more complex as you can see!

Example in Simple Terms:
If you had two arrays like {2, 4, 6} and {1, 3, 5}, SUMPRODUCT would:

• Multiply: (2×1), (4×3), (6×5) → {2, 12, 30}
• Then add: 2 + 12 + 30 = 44

As you can see, this is a much more complex type of formula, which is why it’s only taught in our Advanced Excel courses.

Simple Example

Let’s see SUMPRODUCT in action with a simple example.

Suppose you have the following dataset:

You want to calculate the total sales value by multiplying each item’s Quantity by its Price, and then adding up the results.

Step-By-Step Walkthrough

1. Start with the Formula:

In any empty cell, type:

=SUMPRODUCT(B2:B4, C2:C4)

• B2:B4 refers to the Quantity column.
• C2:C4 refers to the Price column.

2. Behind the Scenes:

• Excel will automatically multiply each row’s Quantity × Price:
  • Apples: 10 × 5 = 50
  • Bananas: 5 × 3 = 15
  • Oranges: 8 × 2.5 = 20
• Then, it adds up all the results:
  • 50 + 15 + 20 = 80

3. Final Result:
   You’ll get 80 — the total sales value!

Tip: If your data isn’t clean enough to easily use functions on, using Excels Data Cleaning can help ensure you get accurate results!

Using SUMPRODUCT With Criteria

SUMPRODUCT becomes even more powerful when you add criteria to your calculations.

You can tell Excel to include or exclude values based on conditions. No extra helper columns needed in this case.

This allows you to do conditional sums, counts, and even weighted averages all inside a single formula.

How To Apply Criteria

You can add logical conditions inside your SUMPRODUCT formula by using multiplication to combine TRUE/FALSE checks.

For example:

(A2:A10=”Apples”)*(B2:B10)

• (A2:A10=”Apples”) checks if the item is “Apples” (TRUE or FALSE).
• Multiplying by B2:B10 applies the quantity only where the condition is TRUE.

Example 1: Calculating Sales by Category

Suppose you only want the total sales for Apples.

Use this formula:

=SUMPRODUCT((A2:A4=”Apples”)*(B2:B4)*(C2:C4))

What It Does:

• Filters the rows where Item = Apples.
• Multiplies Quantity × Price for Apples only.
• Sums the filtered results.

This formula will then output 50! Multiplying the 10 and 5 in the apples row.

Example 2: Conditional Counts

You can also count how many sales meet a condition.

To count how many products had more than 5 units sold:

=SUMPRODUCT(–(B2:B4>5))

• (B2:B4>5) creates TRUE/FALSE values.
• The double negative (–) converts TRUE into 1 and FALSE into 0.
• SUMPRODUCT then adds up the 1s — giving the count.

Tip: Always double-check parentheses when mixing conditions. It’s a common source of mistakes!

Example 3: Weighted Averages With Criteria

Want to find the weighted average price for Oranges only?

Use:

=SUMPRODUCT((A2:A4=”Oranges”)*(B2:B4)*(C2:C4)) / SUMPRODUCT((A2:A4=”Oranges”)*(B2:B4))

Breaking It Down:

• The top part calculates the total weighted sales (Quantity × Price for Oranges).
• The bottom part sums just the quantities for Oranges.
• Dividing the two gives the weighted average price.

Related: Need to highlight differences quickly? Learn how to Compare Two Columns In Excel.

Conclusion

The Excel SUMPRODUCT function is an incredibly powerful and flexible tool for anyone looking to move beyond basic summing and simple conditions.

With SUMPRODUCT, you can:

• Multiply and add arrays effortlessly in a single step.
• Apply multiple conditions without complicated nesting.
• Calculate weighted averages, conditional sums, and counts — all without needing helper columns or advanced array formulas.

It’s the perfect choice when you need more control and flexibility than what SUMIFS or basic functions can offer.

If you’re just starting out, experiment with small datasets first to build your confidence.

Then, gradually apply SUMPRODUCT in larger, more complex spreadsheets — whether you’re tracking sales, building dashboards, or analyzing business performance.

Mastering this function will save you time, simplify your formulas, and make you a much more powerful Excel user.