Spreadsheet Practice

SUMPRODUCT function

Quick answer

SUMPRODUCT multiplies matching items in two or more ranges and adds the results.

Syntax
=SUMPRODUCT(array1, [array2], ...)
Most common use
=SUMPRODUCT(B2:B5,C2:C5) returns $6,585 (Total revenue: units × price for every row, added up)

It gives revenue from units and price columns, weighted averages, and conditional totals, all in one cell and without Ctrl+Shift+Enter in any version.

Available in
Every current version
Category
Math functions
Returns
The sum of the row-by-row products.
Google Sheets
Works the same in Google Sheets.

Syntax

=SUMPRODUCT(array1, [array2], ...)

ArgumentRequired?What it is
array1RequiredThe first range of numbers, such as units.
array2OptionalMore ranges the same size, such as prices. Each row is multiplied together before adding.

Examples

Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.

CellFormulaResultWhat it shows
F2=SUMPRODUCT(B2:B5,C2:C5)$6,585Total revenue: units × price for every row, added up
F3=SUMPRODUCT((D2:D5="East")*B2:B5*C2:C5)$3,820Revenue for East only: the test returns 1 or 0 for each row
F4=SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5)$61.54Average price weighted by units sold

Try it live

This is the sample sheet. Change any number or text, or edit a formula, and the results update.

Show the sample data as a table
ProductUnitsPriceRegion
Widget40$52East$6,585
Gadget25$89West$3,820
Gizmo12$145East$61.54
Doohickey30$18West

Practice SUMPRODUCT

From lesson 35, SUMPRODUCT. Graded instantly, then re-checked on changed data.

Total revenue

In G2, calculate total revenue (units × price for every row) in one formula.

Common mistakes

What you seeWhy, and how to fix it
#VALUE!The ranges are different sizes, such as B2:B5 and C2:C6. Make every range cover the same rows.
The result is 0A condition passed as its own argument, as in =SUMPRODUCT(D2:D5="East",B2:B5), gives TRUE and FALSE, which SUMPRODUCT counts as 0. Multiply instead, or add -- in front: --(D2:D5="East").
The workbook is slowWhole-column references like A:A make SUMPRODUCT multiply a million rows. Use exact ranges or a table.

Tips

  • Count rows meeting two conditions in any version: =SUMPRODUCT((B2:B50="East")*(C2:C50>1000)).

Questions

How do I calculate a weighted average in Excel?

Divide SUMPRODUCT by the total of the weights: =SUMPRODUCT(B2:B10,C2:C10)/SUM(B2:B10).

Can SUMPRODUCT replace SUMIFS?

Yes, and it can test conditions SUMIFS can't, such as =SUMPRODUCT((MONTH(A2:A50)=3)*C2:C50). SUMIFS is faster and easier to read when it can do the job.

Why does SUMPRODUCT return 0?

Usually because TRUE/FALSE conditions were passed as separate arguments. Multiply them into the other range, or use -- to turn them into 1s and 0s.

Lessons that use SUMPRODUCT

Answers that use SUMPRODUCT

In other languages

SUMPRODUCT is SUMAPRODUCTO in Spanish Excel and SUMMENPRODUKT in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.

Updated Checked by running: every formula on this page was run by our formula engine on .