Summary

The Excel SUMPRODUCT function multiplies corresponding elements in arrays and then adds those products together. In its simplest form, it is a compact way to calculate totals such as quantity times price, hours times rate, or weight times score without creating a helper column.

Its real value appears when you combine those multiplications with logical conditions. A test such as (A1:A4="A") produces TRUE and FALSE values, which can be converted into 1s and 0s inside the formula. That lets SUMPRODUCT act as a conditional sum, conditional count, or weighted calculation in a single expression.

Syntax

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

The default behavior is multiplication followed by addition. Microsoft also notes that you can build other arithmetic patterns by using operators such as *, +, -, and / directly inside the formula.

Arguments

• array1 - [required] The first array argument.
• array2, array3, ... - [optional] Additional arrays of the same size.

All array arguments must have the same dimensions. If they do not, SUMPRODUCT returns #VALUE!.

SUMPRODUCT vs Related Functions

SUMPRODUCT is more flexible than SUMIFS, but that flexibility comes with more responsibility. You have to manage the array logic yourself, including shape, coercion of TRUE/FALSE values, and performance on large ranges.

Using SUMPRODUCT

A standard pattern is =SUMPRODUCT(qty,price). Each row is multiplied first, and then the results are summed. This removes the need for a separate revenue column while keeping the final formula explicit about how the total is produced.

A second pattern is conditional filtering. If a logical test is multiplied by a numeric array, rows that fail the test effectively become zero and disappear from the final total. This is why formulas such as =SUMPRODUCT((Region="East")*Sales) work even though there is no dedicated criteria argument.

SUMPRODUCT also handles counting formulas. If the expression inside the function returns 1 for matching rows and 0 for nonmatching rows, the final sum is the count of matches. That makes it useful not only for totals, but also for rule-based counts that go beyond basic COUNTIF patterns.

Example 1 - Multiply Two Columns and Sum the Result

This is the classic weighted-total example.

=SUMPRODUCT(B1:B4,C1:C4)

Each quantity is multiplied by its matching price, and all four products are added together. With the sample data, the result is 3140. This is the basic form to remember whenever one column is a weight and the other is a value.

Example 2 - Apply a Single Condition

Logical tests can be used as row filters inside the multiplication.

=SUMPRODUCT((A1:A4="A")*C1:C4)

The comparison returns TRUE for rows 1 and 3, which are then treated as 1, while the other rows become 0. The result is 250, which is the sum of the C-values for the rows marked "A".

Example 3 - Filter and Weight in the Same Formula

This example combines conditional filtering with row-by-row multiplication.

=SUMPRODUCT((D1:D4="Active")*B1:B4,C1:C4)

Only the rows marked "Active" are included in the revenue calculation. In the sample data, row 3 is excluded before the quantity-price multiplication is summed, so the result is 1940.

Example 4 - Count Values Above a Threshold

SUMPRODUCT can also be used as a conditional counter.

=SUMPRODUCT((B1:B4>G1)*1)

The comparison returns TRUE for values greater than the threshold in G1 and FALSE otherwise. Multiplying by 1 converts those logical results into 1s and 0s, so the final sum becomes a count. With G1 equal to 5, the result is 2.

Microsoft also recommends avoiding full-column references in SUMPRODUCT. A formula such as =SUMPRODUCT(A:A,B:B) forces Excel to process more than a million rows in each array. Bounded ranges are usually the better choice for both speed and clarity.

• All arrays must have the same dimensions.
• Non-numeric entries in arrays are treated as 0.
• Bounded ranges perform much better than full-column references.

Conclusion Recap

• Summary: SUMPRODUCT multiplies matching array elements and sums the results.
• Core use: Weighted totals such as quantity times price.
• Flexible use: Conditional sums and counts built from array logic.
• Main rule: All arrays must be the same size.
• Performance note: Avoid full-column references when possible.