Sheetsdrill

Math Updated November 2026

SUMPRODUCT

SUMPRODUCT(array1, [array2, ...]) multiplies the ranges element by element and returns the sum of those products, so SUMPRODUCT(B2:B10, C2:C10) is the total of quantity times price without a helper column. Because TRUE/FALSE comparisons turn into 1 and 0 when multiplied, it also works as a flexible conditional sum.

Syntax

=SUMPRODUCT(array1, [array2, ...])
PartWhat it is
array1A range of numbers, or a comparison such as (A2:A10="West").
array2Optional further ranges of the same size, multiplied row by row with the first.

Examples

=SUMPRODUCT(B2:B10, C2:C10)

Result: Total revenue: each row's units times unit price, added up

=SUMPRODUCT(B2:B10, C2:C10)/SUM(B2:B10)

Result: A weighted average price, weighted by units

=SUMPRODUCT((A2:A100="East")+(A2:A100="West"), D2:D100)

Result: The sum of column D for East or West rows (an OR that SUMIFS cannot do)

Common mistakes

  • #VALUE! because the ranges are different sizes; use the same rows in every array.
  • Text in a numeric range is treated as 0 silently, so a total that looks low may contain text-numbers; convert with VALUE.
  • Writing a condition without converting it, SUMPRODUCT((A2:A10="West")) returns 0 in some cases; multiply by 1 or use -- to coerce booleans.

Practice it

The drills on Sheetsdrill check your answer on live data, free, no account needed.

Start practicing

Questions

Is SUMPRODUCT the same as SUM(A*B)?

Yes: =ARRAYFORMULA(SUM(B2:B10*C2:C10)) returns the same number, but SUMPRODUCT needs no wrapper.

When should I use SUMPRODUCT instead of SUMIFS?

When the condition is a calculation (a month of a date, a length of text) or needs OR logic. For plain equal and greater-than conditions SUMIFS is faster.

Related

  • SUM and AVERAGEHow SUM and AVERAGE work in Google Sheets, with examples for a column total, average order value, share of total with locked references, and why you get 0.
  • SUMIFSHow SUMIFS in Google Sheets adds values that meet several conditions at once, with examples for a rep and month, date ranges, not-equal and wildcards.
  • ARRAYFORMULAHow ARRAYFORMULA in Google Sheets fills a whole column from one cell, with examples for multiplying columns, IF across a range, and the common errors.
  • COUNTIFSHow COUNTIFS in Google Sheets counts rows where several conditions are true, with examples for status and priority, date ranges and not-equal, plus errors.

Previous: ROUNDNext: LEN