SUMPRODUCT multiplies matching numbers in two or more ranges and adds up the results.
Syntax
=SUMPRODUCT(array1, [array2], ...)
Total sales from quantity and price
With quantity in B2:B10 and price in C2:C10:
=SUMPRODUCT(B2:B10, C2:C10)
This is the same as multiplying each row and adding, without a helper column.
Weighted average
=SUMPRODUCT(B2:B10, C2:C10) / SUM(B2:B10)
Useful for weighted grades or average price weighted by quantity.
Conditions inside SUMPRODUCT
Count rows that meet two conditions:
=SUMPRODUCT((A2:A100="North")*(B2:B100="Laptop"))
Sum values that meet them:
=SUMPRODUCT((A2:A100="North")*(B2:B100="Laptop")*C2:C100)
OR conditions
Use + instead of *: =SUMPRODUCT(((A2:A100="North")+(A2:A100="South")>0)*C2:C100).
Tips
- All ranges must be the same size.
- Text in a numeric range is treated as zero.
- For simple conditions, SUMIFS is easier to read and faster.
Worked example
Quantity is in B2:B5 (10, 5, 8, 2) and price in C2:C5 (100, 200, 50, 400). =SUMPRODUCT(B2:B5,C2:C5) multiplies row by row and adds: 1000 + 1000 + 400 + 800 = 3200. With a condition: =SUMPRODUCT((A2:A5="North")*B2:B5*C2:C5).
Common mistakes
- Using ranges of different sizes. This gives #VALUE!.
- Using a comma between a condition and a range, for example
(A2:A5="North"),B2:B5. Text and TRUE/FALSE values are treated as 0. Use * to multiply.
- Including header cells in the range.
Frequently asked questions
What is SUMPRODUCT used for?
Multiplying two lists and adding the results, such as quantity times price, and conditional sums with several conditions.
Can SUMPRODUCT replace SUMIFS?
Yes, and it can do OR logic and calculations inside conditions. SUMIFS is faster on large data.
Do I need Ctrl+Shift+Enter?
No. SUMPRODUCT handles arrays by itself.
Practice this online, free
Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.
Practice these formulas: SUMPRODUCT, SUMIFS, COUNTIFS.
Start Excel practice → Case studies
