SUMPRODUCT Function

Summary

The Excel SUMPRODUCT function multiplies corresponding values from two or more arrays and then adds those products together. If one row has a price of 10 and a quantity of 2, SUMPRODUCT contributes 20 from that row. It repeats that process across the full set and returns a single total.

That makes SUMPRODUCT useful whenever the calculation follows a repeated row-by-row pattern. Common examples include inventory value, weighted scores, commission models, and conditional counts or sums built from logical tests. It is often used as a compact alternative to helper columns because the multiplication and final addition happen inside one formula.

SUMPRODUCT is useful in statistical work because it can combine multiplication and totaling across paired arrays in one formula. That makes it practical in weighted calculations, covariance-style ideas, and many advanced formulas where array logic matters.

Syntax

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

Each array must line up with the others. For example, =SUMPRODUCT(A1:A3,B1:B3) multiplies A1*B1, A2*B2, and A3*B3, then adds those three results. If the arrays do not have the same dimensions, Excel returns #VALUE!.

Arguments

• array1 - The first array or range.
• array2, ... - Additional arrays or ranges to multiply with the first array.

Microsoft documents up to 255 arguments. In practice, the important rule is that the arrays must be the same size if you want the multiplication to work row by row.

SUMPRODUCT vs Other Functions

SUMPRODUCT overlaps with several functions, but its strength is combining multiplication with aggregation in one step.

A practical caution: SUMPRODUCT can become heavy if it is pointed at full-column references such as A:A and B:B. Limiting the ranges to the actual used rows keeps the workbook more efficient.

Using the SUMPRODUCT Function

The most direct use of SUMPRODUCT is a matched-pair total. If one column contains prices and another contains quantities, SUMPRODUCT returns the total value of all rows without needing a separate helper column for price * quantity. The same idea applies to labor cost models, shipping cost calculations, or any worksheet where each row contributes one product to the final total.

SUMPRODUCT is also common in weighted analysis. A weighted grade, weighted average, or weighted contribution model depends on multiplying each measure by its weight before summing. Because the function performs that pattern natively, the resulting formula is usually shorter and easier to audit than several intermediate calculations spread across the sheet.

A third use is conditional logic. When a logical test such as (A1:A10="Blue") is converted to 1s and 0s with the double unary operator, SUMPRODUCT can count or filter rows without using helper columns. That pattern is widely used in older workbooks and still appears in many models where a compact conditional formula is needed.

• Use SUMPRODUCT for inventory totals, rate-by-hours totals, and weighted scores.
• Use it when each row follows the same multiplication pattern.
• Use logical tests inside SUMPRODUCT when you need compact conditional counts or sums.

Example 1 - Find the Total Inventory Cost

This formula multiplies each item price by its matching quantity and then adds the row totals together. For the sample data, it evaluates 10*2 + 20*5 + 50*1, which returns the full inventory value in one step.

=SUMPRODUCT(B1:B3, D1:D3)

Example 2 - Calculate a Weighted Grade

Weighted grading works because each score contributes in proportion to its assigned weight. Here, homework contributes 20% and the exam contributes 80%, so the formula multiplies each score by its weight before adding the weighted contributions.

=SUMPRODUCT(B1:B2, D1:D2)

Example 3 - Count Matching Rows with a Logical Test

The expression (A1:A3="Blue") produces TRUE/FALSE results. The double minus converts those logical values into 1s and 0s, and SUMPRODUCT adds them. The final result is the count of rows that contain "Blue".

=SUMPRODUCT(--(A1:A3="Blue"))

Example 4 - Sum Total Project Spend

This is the same structure as the inventory example, but the paired values are hourly rates and hours worked. Each row produces one cost figure, and SUMPRODUCT combines those row costs into the total project spend.

=SUMPRODUCT(B1:B3, D1:D3)

Conclusion Recap

SUMPRODUCT is useful when the worksheet follows the same multiply-then-add pattern across many rows. This lesson showed why it works so well for things like inventory totals, weighted grades, and total project cost without needing extra helper columns.

The examples also showed that SUMPRODUCT can do more than cost totals. It can work with logical tests too, which makes it helpful for compact counting and filtering formulas. The main rule to remember is simple: the arrays need to line up in size.

• Main job: SUMPRODUCT multiplies corresponding items and adds the products.
• Core requirement: The arrays must have matching dimensions.
• Strong use cases: Inventory value, weighted results, and conditional counting.
• Behavior note: In array arguments, nonnumeric entries are treated as 0.