Quick Answer

SUMPRODUCT Function

Sums the products of corresponding array elements; great for conditional calculations.

Syntax

SUMPRODUCT

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

Parameters

Parameter	Description	Required
array1	The first array or range whose elements will be multiplied and summed.	Required
array2	Additional arrays/ranges. All arrays must have the same dimensions.	Optional
array3	Further arrays/ranges (optional). Up to 255 in modern Excel.	Optional

Basic Example

Weighted sum of two columns

=SUMPRODUCT(A1:A3, B1:B3)

Result32

With A=(1,2,3) and B=(4,5,6): 1×4 + 2×5 + 3×6 = 4 + 10 + 18 = 32.

Advanced Examples

Example 1: Conditional sum with criteria

Sum amounts in B where category in A equals "Apple".

Use a boolean expression that SUMPRODUCT coerces to 1/0.

=SUMPRODUCT((A1:A10="Apple")*(B1:B10))

Result: Sum of matching amounts

(A1:A10="Apple") yields TRUE/FALSE; multiplying by B coerces TRUE to 1 and FALSE to 0, so only Apple rows contribute.

Example 2: Count rows meeting a condition

Count how many values in A exceed 5.

Coerce the boolean to a number and sum.

=SUMPRODUCT((A1:A10>5)*1)

Result: Count of values > 5

(A1:A10>5) gives TRUE/FALSE; multiplying by 1 converts to 1/0, and SUMPRODUCT adds them up.

How SUMPRODUCT Works

SUMPRODUCT takes the corresponding elements of each array, multiplies them together per position, and sums the results. With a single array it simply sums that array. Boolean criteria must be coerced to numbers (using * or --) because SUMPRODUCT does not auto-convert TRUE/FALSE.

1

Prepare equal-sized ranges

Ensure all arrays have the same dimensions.

2

Enter the formula

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

3

Press Enter

The sum of products appears. (No array-entry needed in modern Excel.)

Important Notes & Limitations

All array arguments must have the same dimensions, or SUMPRODUCT returns #VALUE!.
Boolean expressions must be coerced with * or --; raw TRUE/FALSE cannot be multiplied directly.
Text and empty cells within the ranges are treated as 0, which can hide data-type issues.

Common Errors & Fixes

#VALUE!Arrays have mismatched sizes, or a boolean array wasn't coerced to numbers.

Fix: Make all ranges the same size and multiply criteria by 1 or another numeric array.

Wrong totalText/blank cells were silently treated as 0.

Fix: Verify the ranges contain the expected numeric values.

Download Practice File

Practice SUMPRODUCT with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the SUMPRODUCT function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Related Functions

ABS

ABS converts negative numbers to positive, leaving positive numbers unchanged.

ACOS

Returns the angle (in radians) whose cosine is the supplied number.

ASIN

Returns the angle (in radians) whose sine is the supplied number.

ATAN

Returns the angle (in radians) whose tangent is the supplied number.

ATAN2

Returns the angle in radians to a point given by its x and y coordinates.

CEILING.MATH

Rounds up to a multiple of significance, with a mode for negative numbers.

Frequently Asked Questions

Do I need to press Ctrl+Shift+Enter?

No. In modern Excel and Google Sheets, SUMPRODUCT handles arrays natively without special array-entry.

Why multiply a condition by another range?

A condition like (A1:A10="Apple") returns TRUE/FALSE; multiplying by a numeric range coerces TRUE→1 and FALSE→0, effectively filtering the sum.

What's the difference between SUMPRODUCT and SUMIFS?

SUMIFS is simpler for straightforward conditional sums, but SUMPRODUCT is more flexible (it can combine multiple AND/OR logic, weighted sums, and counts) in a single formula.