How to Use SUMPRODUCT Function in Excel + Tips

Hello everyone! Today i will show you how to use SUMPRODUCT function in Excel step by step.

What is SUMPRODUCT Function in Excel?

The SUMPRODUCT function multiplies corresponding numbers from two or more ranges together, and then adds up all the results! It allows you to calculate total revenue (Qty × Price) or weighted averages in 1 single formula without creating extra helper calculation columns!

📚 Related Tutorial: Want to calculate conditional totals using simple criteria? Check out SUMIF vs SUMIFS in Excel: Easy Guide!

🛠️ Formula Syntax

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

• array1: First range of numbers or conditions to multiply (example: B2:B4).
• array2, array3, ... (Optional): Second and additional ranges of same size to multiply together (example: C2:C4).

📋 Simple Follow-Along Examples

Example 1: Calculate Total Revenue (Quantity × Price)

Suppose you have this product inventory table in cell range A1:C4:

Formula:

=SUMPRODUCT(B2:B4, C2:C4)

Step-by-Step Calculation:

(10 × $2) + (5 × $3) + (8 × $1)
= $20 + $15 + $8
= $43

👉 Result: $43 (Total revenue calculated without extra helper column!)

Example 2: Calculate Weighted Average Grade

Suppose you have student exam scores with weight percentages:

Formula:

=SUMPRODUCT(B2:B4, C2:C4) / SUM(C2:C4)

Calculation:

(80 × 0.3) + (90 × 0.5) + (100 × 0.2)
= 24 + 45 + 20
= 89%

👉 Result: 89% (Final weighted average grade).

Example 3: Total Sales for Specific Product Only (Apple Only)

Calculate sales for "Apple" only:

=SUMPRODUCT((A2:A4="Apple") * B2:B4 * C2:C4)

Condition (A2:A4="Apple") creates TRUE/FALSE values which Excel automatically converts to 1 and 0!

Example 4: Multiple Conditions with AND (* Operator)

Suppose you have this sales table:

Calculate total Apple sales in the East region:

=SUMPRODUCT((A2:A5="Apple") * (B2:B5="East") * C2:C5)

Calculation: 100 + 120 = 220

Example 5: Count Rows with Multiple Conditions

Count how many rows match both Product = Apple AND Region = East:

=SUMPRODUCT((A2:A5="Apple") * (B2:B5="East"))

👉 Result: 2

⚡ SUMPRODUCT vs SUMIFS Comparison

💡 Quick Tips & Easy Memory Rule:

1. Memory Rule: SUMPRODUCT = Multiply first ➔ Add everything together.
2. Same Range Size Requirement: All array ranges inside SUMPRODUCT must have exact same number of rows and columns (example: B2:B10 and C2:C10). If range sizes don't match (like B2:B10 and C2:C5), Excel will return #VALUE! error!
3. Boolean Multiplier *: Using * automatically converts TRUE/FALSE conditions into 1 and 0 for calculation!

📚 Related Resources & Tutorials

• SUMIF vs SUMIFS in Excel: Easy Guide
• How to Use SUBTOTAL Function in Excel
• How to Use SUM Function in Excel
• Top 15 Basic Excel Formulas for Beginners ↗