SUMPRODUCT

Multiply matching entries across arrays and return the total of those products.

Math & Trigonometry
|
Excel All versions
|
Google Sheets Supported

Spreadsheet editor

Syntax

=SUMPRODUCT(array1, [array2, ...]) Returns: Number

Arguments

Argument Required Description
array1 Yes The first array or range whose values you want to multiply and then sum.
array2 No Additional arrays or ranges to multiply element-by-element with array1 before summing. All arrays must be the same size.

About

SUMPRODUCT takes two or more arrays (or ranges) of the same size, multiplies each pair of matching entries together, and adds up all the results into a single number. With just one array, it simply totals the values.

This is one of the most practical functions in Excel. Use it to calculate total revenue from quantity and price columns, compute weighted averages (when combined with SUM), or build conditional totals without needing helper columns. Because SUMPRODUCT naturally works with arrays, you can embed logical tests directly inside it, like =SUMPRODUCT((A2:A10="East")*(B2:B10)), to sum values that match a condition.

For straightforward conditional sums, SUMIF or SUMIFS are simpler. SUMPRODUCT shines when you need to combine multiple conditions with custom logic, or when you want to multiply and sum across several columns in one step.

Examples

Weighted average with SUMPRODUCT and SUM

Try calculating a weighted average by combining SUMPRODUCT with SUM. Edit the grades or credit hours and watch the weighted average update instantly.

Spreadsheet editor

Conditional sum with embedded logic

See how SUMPRODUCT filters data without SUMIF. The condition (A2:A6="East") creates an array of 1s and 0s, then multiplying by the sales column keeps only East region values. Change a region label and watch the total shift.

Spreadsheet editor

Watch out for

⚠

Mismatched array sizes

If the arrays you pass have different numbers of rows or columns, SUMPRODUCT returns a #VALUE! error.

→ Double-check that every array argument covers the same number of cells. For example, B2:B10 and C2:C10 both have 9 rows.

⚠

Text or blank cells in numeric arrays

Text values in a range cause a #VALUE! error because SUMPRODUCT can't multiply text by a number.

→ Clean up the data so numeric columns contain only numbers, or wrap values with a conversion like multiplying by 1 (e.g., B2:B10*1) to force errors you can handle with IFERROR.

⚠

Using SUMPRODUCT when SUMIFS is simpler

Building conditional sums with SUMPRODUCT and embedded TRUE/FALSE logic when a straightforward criteria-based function would do the same job more clearly.

→ For basic conditional sums, prefer SUMIFS. Save SUMPRODUCT for cases where you need custom logic, calculated criteria, or multiplication across multiple columns.

Tips & notes

When you pass a single array, SUMPRODUCT just totals its values (same as SUM). Non-numeric entries like TRUE/FALSE are treated as 1/0 when multiplied by another array, which is why embedded logical tests work. All arrays must have identical dimensions or you'll get a #VALUE! error.

Common questions

Can SUMPRODUCT replace SUMIFS?

In many cases, yes. By embedding conditions like (A1:A10="North") inside SUMPRODUCT, you can filter and sum in one formula. However, SUMIFS is faster on large datasets and easier to read for simple criteria.

Does SUMPRODUCT need Ctrl+Shift+Enter?

No. SUMPRODUCT handles arrays natively, so you just press Enter. You only need Ctrl+Shift+Enter for regular formulas that don't support arrays on their own.

How do I calculate a weighted average with SUMPRODUCT?

Divide SUMPRODUCT(values, weights) by SUM(weights). For example, =SUMPRODUCT(B2:B5, C2:C5) / SUM(C2:C5) gives the weighted average of B2:B5 using weights in C2:C5.