How do I multiply two columns row by row and add up the results?
SUMPRODUCT multiplies each row of one range by the matching row of another range, then adds up all the results in one step: =SUMPRODUCT(range1, range2). It is the standard way to calculate a weighted total, like quantity times price, without a helper column.
Formula (Excel and Google Sheets)
=SUMPRODUCT(B2:B10, C2:C10)SUMPRODUCT pairs up each row across the ranges you give it, multiplies the values in that row together, and adds every row's result into one number. =SUMPRODUCT(B2:B10, C2:C10) is the same result as adding a helper column that computes B2 times C2, B3 times C3, and so on, then summing that column, except SUMPRODUCT does it in one formula with no helper column needed.
SUMPRODUCT can also express conditions COUNTIFS cannot, like OR logic, by multiplying TRUE/FALSE tests together: =SUMPRODUCT((A2:A10="Northeast")+(A2:A10="Southeast"), B2:B10) sums column B for rows where column A is either region, since TRUE/FALSE tests are treated as 1 and 0 inside the multiplication.
Step by step
- 1
Make sure every range is the same size
SUMPRODUCT lines up ranges row by row, so they must all cover the same number of rows.
- 2
For a plain weighted total, multiply two ranges
=SUMPRODUCT(quantity_range, price_range) gives you total revenue without a helper column.
- 3
For conditional totals, multiply TRUE/FALSE tests
Wrap a comparison in parentheses, e.g. (A2:A10="Northeast"), and multiply it against the range you want to sum or against another condition.
Common questions
Do I need to enter SUMPRODUCT as an array formula with Ctrl+Shift+Enter?
No. Unlike some array formulas, SUMPRODUCT works as a normal formula in both Excel and Google Sheets; just press Enter.
How is SUMPRODUCT different from SUMIFS for conditional totals?
SUMIFS only combines conditions with AND. SUMPRODUCT can express AND, OR, and row-by-row multiplication (like quantity times price), which SUMIFS cannot do on its own.
Related formulas
How to Use SUMIF and SUMIFS in Excel and Google Sheets
RelatedHow to Count Rows That Match Multiple Criteria with COUNTIFS
RelatedPivot Table Basics: How to Summarize Data Without Formulas
From the blogHow to Make Sense of Your Spreadsheet Data Without Being an Analyst
Want a formula written for your exact spreadsheet, not a generic example?
Try the free formula generator