SUMPRODUCT Function in Excel: Complete Tutorial with Examples

Need to multiply arrays and sum the results? SUMPRODUCT handles complex calculations that combine multiplication with summation, making it perfect for weighted averages, multi-criteria counting, and advanced data analysis. It’s one of Excel’s most powerful functions.

What Makes SUMPRODUCT Useful

SUMPRODUCT multiplies and sums in one step:

• Weighted calculations – Calculate weighted averages or totals
• Multiple criteria – Count or sum with complex AND/OR conditions
• Array operations – Handle calculations across multiple ranges simultaneously
• Conditional logic – Use TRUE/FALSE conditions for filtering
• No array entry needed – Works without Ctrl+Shift+Enter in older Excel

The Syntax

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

Arrays are ranges to multiply together, then sum. All arrays must be the same size.

Example 1: Basic Multiplication and Sum

Calculate total cost (quantity × price):

Formula: =SUMPRODUCT(B2:B4, C2:C4)

Result: $5,100

Calculates: (5×800) + (20×25) + (10×60) = 4000 + 500 + 600 = 5100

Example 2: Weighted Average

Calculate weighted average grade:

Formula: =SUMPRODUCT(B2:B4, C2:C4)

Result: 86.1

Calculates: (85×0.2) + (78×0.3) + (92×0.5) = 17 + 23.4 + 46 = 86.1

Example 3: Count with Multiple Criteria

Count sales in West region over $1,000:

Formula: =SUMPRODUCT((B2:B6="West")*(C2:C6>1000))

Result: 2

Laptop and Monitor meet both criteria. Parentheses create TRUE/FALSE arrays, multiplication combines them.

Example 4: Sum with Multiple Criteria

Sum sales for West region laptops:

Formula: =SUMPRODUCT((A2:A5="Laptop")*(B2:B5="West")*C2:C5)

Result: $9,300

Both conditions must be TRUE (Laptop AND West), then sum those sales.

Example 5: OR Logic with Multiple Criteria

Count orders from West OR East regions:

Formula: =SUMPRODUCT((B2:B5="West")+(B2:B5="East"))

Result: 2

Addition (+) creates OR logic. Either condition being TRUE counts.

Example 6: Advanced Conditional Counting

Count products with “Pro” in name AND price over $500:

Formula: =SUMPRODUCT((ISNUMBER(SEARCH("Pro", A2:A5)))*(B2:B5>500))

Result: 2

SEARCH finds “Pro” in names, ISNUMBER converts to TRUE/FALSE, multiplication combines with price condition.

SUMPRODUCT vs Similar Functions

How TRUE/FALSE Math Works

TRUE = 1, FALSE = 0

(B2:B5="West") creates: {TRUE, FALSE, TRUE, FALSE}
Becomes: {1, 0, 1, 0}

Multiply by another condition:
{1, 0, 1, 0} * {TRUE, TRUE, FALSE, TRUE}
= {1, 0, 0, 0}

Only rows where BOTH are TRUE give 1.

Common Patterns

Count with two criteria:

=SUMPRODUCT((range1=criteria1)*(range2=criteria2))

Sum with two criteria:

=SUMPRODUCT((range1=criteria1)*(range2=criteria2)*value_range)

OR logic:

=SUMPRODUCT((range=criteria1)+(range=criteria2))

AND + OR combined:

=SUMPRODUCT((range1=criteria1)*((range2=criteria2)+(range2=criteria3)))

Common Mistakes to Avoid

Different Sized Ranges

• All ranges must be the same size
• B2:B10 with C2:C15 causes error
• Check range dimensions carefully

Forgetting Parentheses

• Conditions need parentheses: (B2:B5=”West”)
• Without them, Excel interprets incorrectly
• Each condition gets its own parentheses

Text Criteria Without Quotes

• (B2:B5=West) is wrong
• (B2:B5="West") is correct
• Numbers don’t need quotes

Error Handling

Handle empty or error cells:

=SUMPRODUCT((A2:A10<>"")*(B2:B10<>"")*C2:C10)

Avoid division by zero:

=SUMPRODUCT((B2:B10<>0)*A2:A10/B2:B10)

Check for valid data:

=SUMPRODUCT((ISNUMBER(B2:B10))*(B2:B10>0)*C2:C10)

Advanced Techniques

Count unique values with criteria:

=SUMPRODUCT((range=criteria)/COUNTIF(unique_range, unique_range))

Weighted average with conditions:

=SUMPRODUCT((region="West")*values*weights)/SUMPRODUCT((region="West")*weights)

Case-sensitive match:

=SUMPRODUCT((EXACT(A2:A10, "Value"))*B2:B10)

Performance Tips

Use SUMIFS when possible For simple criteria, SUMIFS is faster:

SUMIFS is faster than SUMPRODUCT for basic conditions

Limit range size Reference only necessary rows, not entire columns.

Avoid nested functions Keep SUMPRODUCT formulas as simple as possible.

Practical Applications

Sales commission calculation:

=SUMPRODUCT((sales>quota)*sales*commission_rate)

Inventory value:

=SUMPRODUCT(quantity*unit_cost*(category="Electronics"))

Project hours by person:

=SUMPRODUCT((employee=name)*(project=code)*hours)

Tips for Using SUMPRODUCT

Start simple Test each condition separately before combining.

Use helper columns For complex logic, break into steps.

Document logic SUMPRODUCT formulas can get complex. Add comments.

Consider alternatives SUMIFS/COUNTIFS are simpler for basic criteria.

When to Use SUMPRODUCT

Use SUMPRODUCT when:

• Need weighted calculations
• Complex AND/OR logic required
• Multiple criteria across different ranges
• Array calculations without array entry
• SUMIFS/COUNTIFS can’t handle the logic

Don’t use SUMPRODUCT when:

• Simple criteria work with SUMIFS/COUNTIFS
• Performance is critical with large datasets
• Logic is so complex it becomes unreadable

Start Using It

Start with a simple weighted calculation. Multiply two columns and sum the results. Once comfortable, add conditional logic with parentheses. Test each condition before combining.

SUMPRODUCT becomes powerful once you understand TRUE/FALSE multiplication.

Questions about SUMPRODUCT? Need help with complex multi-criteria calculations? Let’s connect.