The PRODUCT and SUMPRODUCT functions in Excel are powerful tools for mathematical calculations. This tutorial will guide you through their usage with clear examples and practical applications.
PRODUCT Function
The PRODUCT function multiplies all numbers given as arguments and returns the product. It’s a quick way to multiply multiple values without using the asterisk (*) operator repeatedly.
Syntax
PRODUCT(number1, [number2], …)
- number1: The first number or range to multiply (required).
- number2, …: Additional numbers or ranges (optional). You can specify up to 255 arguments.
PRODUCT Function Examples
Example 1: Basic Multiplication
To multiply 5, 10, and 2:
=PRODUCT(5, 10, 2)
This will return 100 (5 × 10 × 2).
Example 2: Multiplying a Range
To multiply all values in cells A1:A5:
=PRODUCT(A1:A5)
This multiplies all values in the range together.
Example 3: Compound Interest Calculation
To calculate compound interest growth over multiple periods with rates in B1:B5:
=1000*PRODUCT(1+B1:B5)
If rates are 5%, 6%, 4%, 5%, 3%, this calculates the final value after all growth periods.
Example 4: Combining Ranges and Numbers
To multiply values in A1:A3 by 1.1 (10% increase):
=PRODUCT(A1:A3, 1.1)
This multiplies all values in the range and then multiplies by 1.1.
Interactive PRODUCT Demo
SUMPRODUCT Function
The SUMPRODUCT function multiplies corresponding components in given arrays and returns the sum of those products. It’s one of Excel’s most versatile functions for advanced calculations.
Syntax
SUMPRODUCT(array1, [array2], [array3], …)
- array1: The first array or range whose components you want to multiply and then add (required).
- array2, …: Additional arrays (optional, 2 to 255 arrays). All arrays must have the same dimensions.
Example: SUMPRODUCT({2,3}, {4,5}) = (2×4) + (3×5) = 8 + 15 = 23
SUMPRODUCT Function Examples
Example 1: Basic SUMPRODUCT
To calculate total revenue from quantities in A1:A3 and prices in B1:B3:
=SUMPRODUCT(A1:A3, B1:B3)
If A1:A3 = {10, 20, 15} and B1:B3 = {5, 8, 10}, result is 480 (10×5 + 20×8 + 15×10).
Example 2: Weighted Average
To calculate a weighted average of scores (C1:C4) with weights (D1:D4):
=SUMPRODUCT(C1:C4, D1:D4)/SUM(D1:D4)
This multiplies each score by its weight, sums them, and divides by total weight.
Example 3: Conditional Counting
To count how many cells in A1:A10 are greater than 50:
=SUMPRODUCT((A1:A10>50)*1)
This creates an array of TRUE/FALSE, converts to 1/0, and sums them.
Example 4: Multi-Criteria Calculations
To sum sales (C2:C10) where region (A2:A10) is “East” AND product (B2:B10) is “Widget”:
=SUMPRODUCT((A2:A10=”East”)*(B2:B10=”Widget”)*C2:C10)
This multiplies three conditions together, effectively creating an AND logic.
Interactive SUMPRODUCT Demo (Revenue Calculation)
Quantity Array:
Price Array:
PRODUCT vs SUMPRODUCT
| Feature | PRODUCT | SUMPRODUCT |
|---|---|---|
| Purpose | Multiply all values | Multiply pairs, then sum |
| Result | Single product | Sum of products |
| Arrays | Not required | Works with arrays |
| Complexity | Simple | Advanced |
Excel PRODUCT and SUMPRODUCT Functions – Practical Applications
PRODUCT Applications:
- Calculating compound growth rates over multiple periods
- Volume calculations (length × width × height)
- Probability calculations (multiplying multiple probabilities)
- Scaling factors in engineering and science
- Tax and discount calculations with multiple percentages
SUMPRODUCT Applications:
- Calculating total revenue (quantities × prices)
- Weighted averages (grades, performance metrics)
- Portfolio analysis (shares × prices)
- Conditional counting and summing (alternative to COUNTIFS/SUMIFS)
- Inventory valuation
- Multi-criteria data analysis
Excel PRODUCT and SUMPRODUCT Functions – Real-World Business Examples
Business Case 1: Total Order Value (SUMPRODUCT)
Scenario: Calculate total order value with quantities in A2:A10 and unit prices in B2:B10:
=SUMPRODUCT(A2:A10, B2:B10)
Result: Total revenue from all line items.
Business Case 2: Compound Growth (PRODUCT)
Scenario: Initial investment of $10,000 with annual growth rates of 8%, 12%, 9%, 11% over 4 years:
=10000*PRODUCT(1.08, 1.12, 1.09, 1.11)
Result: Final investment value after compound growth.
Business Case 3: Weighted Performance Score (SUMPRODUCT)
Scenario: Calculate employee score with metrics in C2:C5 and weights in D2:D5:
=SUMPRODUCT(C2:C5, D2:D5)/SUM(D2:D5)
Result: Overall weighted performance score (0-100).
Business Case 4: Conditional Sales Total (SUMPRODUCT)
Scenario: Sum sales (D2:D100) for “Premium” products (C2:C100) sold by “John” (B2:B100):
=SUMPRODUCT((B2:B100=”John”)*(C2:C100=”Premium”)*D2:D100)
Result: Total premium product sales by John.
Excel PRODUCT and SUMPRODUCT Functions – Tips and Tricks
PRODUCT Tips:
- Text and blank cells: PRODUCT ignores text and blank cells (treats as 1).
- Zero handling: If any value is 0, the result is 0.
- Negative numbers: Multiple negative numbers follow standard multiplication rules.
- Efficiency: More efficient than chaining multiplication operators for many values.
SUMPRODUCT Tips:
- Array sizes: All arrays must have the same dimensions or you’ll get a #VALUE! error.
- Boolean logic: Use (condition)*1 to convert TRUE/FALSE to 1/0 for calculations.
- Multiple criteria: Multiply conditions for AND logic: (A1:A10=”X”)*(B1:B10>5)
- OR logic: Add conditions for OR: (A1:A10=”X”)+(A1:A10=”Y”)
- Performance: More efficient than array formulas for large datasets.
- No array entry: Unlike array formulas, no need to press Ctrl+Shift+Enter.
- Text handling: Non-numeric values are treated as 0.
Advanced SUMPRODUCT Techniques
Technique 1: Count Unique Values
To count unique values in range A1:A10:
=SUMPRODUCT(1/COUNTIF(A1:A10, A1:A10))
This counts how many unique entries exist in the range.
Technique 2: Sum Every Nth Row
To sum every 3rd value in A1:A12:
=SUMPRODUCT((MOD(ROW(A1:A12)-1,3)=0)*A1:A12)
This sums values in rows 1, 4, 7, 10, etc.
Technique 3: Date Range Criteria
To sum sales (C2:C100) between two dates in B2:B100:
=SUMPRODUCT((B2:B100>=DATE(2024,1,1))*(B2:B100<=DATE(2024,12,31))*C2:C100)
This sums sales for all of 2024.