SUMPRODUCT Function

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.

SUMPRODUCT is useful because it handles paired multiplication and totaling in one expression. That makes it powerful in weighted calculations and more advanced array logic, where the workbook needs something more flexible than a plain SUM but still readable enough to audit.

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(D1:D4,C1:C4)

The helper column in D marks the rows to include. A value of 1 keeps the row in the total, while 0 removes it. The result is 420, which is the sum of the C-values for the rows marked for inclusion.

Example 3 - Filter and Weight in the Same Formula

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

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

Only the rows marked with 1 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,C1:C4)/SUM(B1:B4)>=G1

The formula turns the revenue total into an average price per unit and compares it to the goal in G1. With G1 set to 130, the result is TRUE.

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

SUMPRODUCT is more advanced than a normal SUM, but the basic idea is still easy to follow: multiply row by row first, then add the results. This lesson showed that pattern clearly with quantity times price, which is one of the most common real uses.

The examples also showed why SUMPRODUCT is powerful. It can filter rows with conditions, do weighted totals, and even count matches without helper columns. It takes more care than SUMIFS, but it gives you more freedom when the logic is not simple.

• 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.