What is the syntax for SUMPRODUCT in Excel?

The syntax for SUMPRODUCT is: =SUMPRODUCT(array1, [array2], [array3], ...)

What are common errors with SUMPRODUCT?

#VALUE! when arrays have different dimensions. Arrays must be same size

What is SUMPRODUCT used for?

SUMPRODUCT multiplies corresponding elements of arrays and sums the results. Basic use: =SUMPRODUCT(Qty, Price) for total value. Advanced use: conditional calculations with TRUE/FALSE arrays that act as 1/0 multipliers.

How does SUMPRODUCT work with conditions?

Conditions create TRUE(1)/FALSE(0) arrays. =SUMPRODUCT((A:A="East")*B:B) multiplies B values by 1 where A is East, 0 otherwise, then sums. Multiple conditions: =SUMPRODUCT((A:A="East")*(C:C>100)*B:B).

What is the difference between SUMPRODUCT and SUMIFS?

SUMIFS is simpler for basic AND conditions. SUMPRODUCT is more flexible: handles OR logic, calculated criteria, and complex expressions. SUMPRODUCT((A:A="X")+(A:A="Y"))*B:B) does OR; SUMIFS cannot.

Can SUMPRODUCT count with multiple criteria?

Yes! =SUMPRODUCT((A:A="East")*(B:B>100)*1) counts rows where A is East AND B>100. The *1 converts TRUE/FALSE to numbers. Equivalent to COUNTIFS but more flexible.

Why is SUMPRODUCT slow with large data?

SUMPRODUCT evaluates entire arrays. For large datasets, SUMIFS is faster for simple conditions. Limit ranges (A1:A1000 not A:A), or use Tables with structured references for better performance.

Math & Trig

SUMPRODUCT Function in Excel

Returns the sum of the products of corresponding ranges or arrays.

Syntax

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

Arguments

array1 (required): First array to multiply
array2 (optional): Second array to multiply
array3 (optional): Additional arrays

Examples

=SUMPRODUCT(A2:A10, B2:B10) - Sum of quantity × price - Result: Total value
=SUMPRODUCT((A:A="East")*(B:B>100)*C:C) - Conditional sum with multiple criteria - Result: Filtered sum
=SUMPRODUCT((A2:A10=D2)*(B2:B10)) - Sum where column A matches D2 - Result: Conditional sum

SUMPRODUCT in reporting workflows

Use absolute references when copying formulas across rows.
For conditional totals see [SUMIF](/functions/sumif/) and [SUMIFS](/functions/sumifs/).
Filtered lists may need [SUBTOTAL](/functions/subtotal/) instead of SUM on visible cells only.

Common errors

#VALUE! when arrays have different dimensions
Arrays must be same size

Use cases

Weighted calculations
Complex conditional sums
Matrix operations
Multi-criteria counting

Frequently asked questions

What is SUMPRODUCT used for? SUMPRODUCT multiplies corresponding elements of arrays and sums the results. Basic use: =SUMPRODUCT(Qty, Price) for total value. Advanced use: conditional calculations with TRUE/FALSE arrays that act as 1/0 multipliers.
How does SUMPRODUCT work with conditions? Conditions create TRUE(1)/FALSE(0) arrays. =SUMPRODUCT((A:A="East")*B:B) multiplies B values by 1 where A is East, 0 otherwise, then sums. Multiple conditions: =SUMPRODUCT((A:A="East")*(C:C>100)*B:B).
What is the difference between SUMPRODUCT and SUMIFS? SUMIFS is simpler for basic AND conditions. SUMPRODUCT is more flexible: handles OR logic, calculated criteria, and complex expressions. SUMPRODUCT((A:A="X")+(A:A="Y"))*B:B) does OR; SUMIFS cannot.
Can SUMPRODUCT count with multiple criteria? Yes! =SUMPRODUCT((A:A="East")*(B:B>100)*1) counts rows where A is East AND B>100. The *1 converts TRUE/FALSE to numbers. Equivalent to COUNTIFS but more flexible.
Why is SUMPRODUCT slow with large data? SUMPRODUCT evaluates entire arrays. For large datasets, SUMIFS is faster for simple conditions. Limit ranges (A1:A1000 not A:A), or use Tables with structured references for better performance.

Editorial review

Reviewed by Excel.Directory Editorial Team. Updated May 2026.