SUMPRODUCT Function in Excel: Multiply Arrays and Add the Results

📎 This article includes 1 downloadable practice file ↓

MathLevel: IntermediateAvailable in: Excel 2007+

In this article

1. Syntax
2. Examples
3. Example 1
4. Example 2
5. Power combo
6. Common errors and fixes
7. SUMPRODUCT in practice
8. Where you will use it
9. Worked example
10. Mistakes people make
11. Related functions
12. Continue learning

SUMPRODUCT multiplies ranges row by row and adds the products. Because it handles arrays natively, it doubles as a flexible conditional sum and counter in every version.

Syntax

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

Argument	What it means
array1…	Ranges of the same size.

Examples

Example 1

=SUMPRODUCT(C2:C100, D2:D100)

Quantity × price for every row, added up.

Example 2

=SUMPRODUCT((MONTH(A2:A100)=3)*E2:E100)

March sales from a date column — something SUMIFS cannot do directly.

Power combo

=SUMPRODUCT(B2:B6, C2:C6)/SUM(C2:C6)

Weighted average: marks weighted by credits.

Common errors and fixes

You see	Why, and the fix
#VALUE!	Arrays of different sizes, or text in a multiplied range.

SUMPRODUCT in practice

Where you will use it

• Total value: quantity × price
• Weighted averages
• Count with complex conditions

Worked example

Running total of qty × price.

Qty	Price	Formula	Result
10	250	=SUMPRODUCT($A$2:A2,$B$2:B2)	2500
4	1200	=SUMPRODUCT($A$2:A3,$B$2:B3)	7300
25	40	=SUMPRODUCT($A$2:A4,$B$2:B4)	8300

Results calculated in Excel.

Mistakes people make

Mistake	What to do instead
Ranges of different sizes	#VALUE!
Text in ranges	Treated as 0

Related functions

SUMIFS · SUM · MMULT

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

Continue learning

All Math & Trig guides →

RANDARRAY Function in Excel: A Whole Grid of Random NumbersRANDARRAY returns an array of random numbers, optionally whole numbers between limits — instant…PERCENTOF Function in Excel: Share of the TotalPERCENTOF returns a subset’s share of a total — mostly used inside GROUPBY and…RAND Function in Excel: Random Decimal Between 0 and 1RAND returns a new random number between 0 and 1 on every recalculation —…RANDBETWEEN Function in Excel: Random Whole Numbers for Test DataRANDBETWEEN returns a random integer between two numbers. It recalculates on every change —…

📎 Practice files for this article

• 📗
  Practice workbook: Math & rounding24 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.
  ⬇ XLSX · 35 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.