SUMPRODUCT function

SUMPRODUCT multiplies arrays element-by-element and sums the results, useful for weighted totals and multi-criteria counting.

=SUMPRODUCT(array1, ...)

Generate a SUMPRODUCT formula

Describe what you need. The generator will reach for SUMPRODUCT where SUMPRODUCT is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How SUMPRODUCT reads its arguments
array1requiredSUMPRODUCT →
ArgumentRequiredDescription
array1RequiredRequired. An array or range of numbers to multiply. Can include comparisons that return TRUE (1) or FALSE (0).
...RepeatingOptional. Additional arrays or ranges of the same size as array1. All arrays must have identical dimensions.

Returns

A number (scalar value), representing the sum of products of array elements.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Calculate total hours logged by Alice

TaskOwnerStart DateDue DateHours Logged
Design mockupsAlice2024-01-022024-01-1012
API developmentBob2024-01-022024-01-2040
TestingCarol2024-01-152024-01-258
DocumentationAlice2024-01-202024-01-306
=SUMPRODUCT((B2:B5="Alice")*(E2:E5))

Result: 18

SUMPRODUCT creates a TRUE/FALSE array where Owner='Alice' (1, 0, 0, 1), then multiplies each by Hours (12, 40, 8, 6). The result is 12+0+0+6=18.

2. Calculate total billable cost by owner at different hourly rates

TaskOwnerStart DateDue DateHours Logged
Design mockupsAlice2024-01-022024-01-1012
API developmentBob2024-01-022024-01-2040
TestingCarol2024-01-152024-01-258
DocumentationAlice2024-01-202024-01-306
=SUMPRODUCT((B2:B5="Alice")*50 + (B2:B5="Bob")*75 + (B2:B5="Carol")*60, E2:E5)

Result: 4230

SUMPRODUCT builds a rates array (50, 75, 60, 50) matching each owner, multiplies by hours (12, 40, 8, 6) to get (600, 3000, 480, 300), then sums to 4230.

3. Count tasks exceeding 10 hours logged

TaskOwnerStart DateDue DateHours Logged
Design mockupsAlice2024-01-022024-01-1012
API developmentBob2024-01-022024-01-2040
TestingCarol2024-01-152024-01-258
DocumentationAlice2024-01-202024-01-306
=SUMPRODUCT((E2:E5>10)*1)

Result: 2

E2:E5>10 produces TRUE/FALSE (1, 1, 0, 0). Multiplying by 1 preserves these values, and SUMPRODUCT sums them to 2.

Common errors

Which SUMPRODUCT error are you seeing?
SUMPRODUCT returned an error#VALUE!
Ensure all ranges span the same row and column count. Use A2:A5 and B2:B5, not A2:A4 and B2:B5.
#REF!
Undo the deletion (Ctrl+Z/Cmd+Z) or update the formula to valid cells. Use absolute references ($A$2:$A$5) to lock ranges and prevent accidental deletion.
#DIV/0!
Guard against zero with conditional logic: =SUMPRODUCT((E2:E5<>0)*A2:A5/(E2:E5+(E2:E5=0))) or wrap with IFERROR to skip invalid rows.
ErrorWhy it happensHow to fix it
#VALUE!Array size mismatch: SUMPRODUCT requires all arrays to have identical dimensions. Multiplying A2:A4 (3 rows) by B2:B5 (4 rows) triggers this error.Ensure all ranges span the same row and column count. Use A2:A5 and B2:B5, not A2:A4 and B2:B5.
#REF!A range in the formula references deleted rows, columns, or sheets. SUMPRODUCT cannot evaluate the missing cells.Undo the deletion (Ctrl+Z/Cmd+Z) or update the formula to valid cells. Use absolute references ($A$2:$A$5) to lock ranges and prevent accidental deletion.
#DIV/0!If using SUMPRODUCT with division (e.g., revenue/hours), and a divisor contains 0, the division fails before SUMPRODUCT can sum.Guard against zero with conditional logic: =SUMPRODUCT((E2:E5<>0)*A2:A5/(E2:E5+(E2:E5=0))) or wrap with IFERROR to skip invalid rows.

Tips and when to use something else

  • SUMPRODUCT treats TRUE as 1 and FALSE as 0, so (range>10) creates an implicit 1/0 array—perfect for counting conditions without COUNTIF.
  • Use SUMPRODUCT for weighted sums and multi-condition logic. For simple filtering on one column, SUMIFS is faster and more readable.
  • SUMPRODUCT works across sheets: =SUMPRODUCT((Sheet2!B:B="Alice")*Sheet2!E:E) retrieves and multiplies data from another sheet transparently.
  • Avoid entire columns (A:A) in large datasets; use explicit ranges (A2:A1000) instead. SUMPRODUCT evaluates every cell, so unbounded ranges slow calculations significantly.

Related functions

SUMIFSUMIF returns the sum of cells in a range that meet a specified criteria, useful for totaling data filtered by a single condition.SUMIFSSUMIFS returns the sum of cells that meet multiple criteria, making it ideal for aggregating data with several conditions at once.PRODUCTReturns the product of all numeric values supplied as arguments, multiplying them together into a single result.SUMSUM adds numeric values in a range or multiple ranges, ignoring text and empty cells, and returns the total sum.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.

Frequently asked questions

Can SUMPRODUCT multiply more than two arrays?
Yes. SUMPRODUCT accepts any number of arrays as long as they share the same dimensions. =SUMPRODUCT(A2:A5, B2:B5, C2:C5) multiplies each element across all three arrays element-by-element, then sums the products.
What's the difference between SUMPRODUCT and SUMIFS?
SUMPRODUCT multiplies arrays and sums the result (perfect for weights, rates, and complex logic). SUMIFS sums a range based on multiple exact match criteria. Use SUMPRODUCT for weighted or mathematical operations; use SUMIFS for filtered simple sums. SUMPRODUCT is more flexible but slower on very large datasets.
How do I count rows meeting multiple conditions with SUMPRODUCT?
Convert each condition to 1/0 using a comparison, then multiply them together: =SUMPRODUCT((B2:B5="Alice")*(E2:E5>10)) counts rows where Owner='Alice' AND Hours>10. Multiplying the arrays ensures both conditions must be true (1×1=1) to contribute to the sum.
Does SUMPRODUCT work in Google Sheets?
Yes, SUMPRODUCT works identically in Google Sheets and Excel. Syntax, behavior, and multi-array multiplication are consistent across both platforms, making it reliable for cross-platform spreadsheets.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17