SUMPRODUCT as a Swiss Army Formula for Conditional Sums

SUMIFS does one job well: add up cells that meet conditions. The moment the question gets more interesting, such as revenue where quantity times price exceeds a threshold, an average weighted by volume or a count of rows where either of two columns says "overdue", it runs out of options. SUMPRODUCT does not. Its odd name hides the fact that it is a general tool for working across arrays inside a single cell. This article shows how it works, the patterns that make it useful and the point where you should stop using it. It extends Excel formulas data people use every week, and the database equivalent is SUM(...) FILTER (WHERE ...) (SUMIF and SUMIFS as GROUP BY).

What it does

SUMPRODUCT(array1, array2, ...) multiplies the elements at the same position in each array and adds the products.

=SUMPRODUCT({1,2,3}, {4,5,6})     ->  1*4 + 2*5 + 3*6 = 32

With ranges, it is the sum of products row by row:

=SUMPRODUCT(B2:B6, C2:C6)

If B holds quantities and C holds unit prices, this is the total revenue without a helper column of B*C. All arrays must have the same dimensions, or the result is #VALUE!. Non-numeric entries (text, blanks) in the arrays are treated as zero, which is helpful and occasionally hides a problem.

Sample data used below:

Conditions are columns of ones and zeros

A comparison such as A2:A6="North" returns an array of TRUE and FALSE. Arithmetic turns those into 1 and 0, so multiplying by the condition keeps the rows where it is true.

=SUMPRODUCT((A2:A6="North") * C2:C6 * D2:D6)

The first factor is 1 for the two North rows and 0 elsewhere. The result is 10*4 + 5*12 = 100. This is "revenue for North", which SUMIFS cannot do directly because it needs the product of two columns.

The same with a comma form: SUMPRODUCT(--(A2:A6="North"), C2:C6, D2:D6). The double negative -- converts booleans to numbers; commas avoid the arithmetic coercion, and the two forms give the same result. The comma form has one advantage: text in C or D is treated as zero rather than raising #VALUE!, which the multiplication form does.

AND and OR

AND is multiplication of conditions:

=SUMPRODUCT((A2:A6="North") * (E2:E6="overdue") * C2:C6 * D2:D6)

North and overdue rows only: 5*12 = 60.

OR is addition, with care for rows that match both:

=SUMPRODUCT(((A2:A6="North") + (A2:A6="South") > 0) * C2:C6 * D2:D6)

North or South: 100 + (8*4 + 2*12) = 100 + 56 = 156. The > 0 turns a row matching two conditions (value 2) back into 1, so it is not counted twice. Without it, overlapping conditions double count. For OR over many values, ISNUMBER(MATCH(A2:A6, {"North","South"}, 0)) gives a compact test.

Counting

COUNTIFS handles conditions on separate columns when they are all AND. For OR across columns, SUMPRODUCT counts rows:

=SUMPRODUCT(--((E2:E6="overdue") + (C2:C6>=20) > 0))

counts rows that are overdue or have a quantity of at least 20: the two overdue rows and the East row, giving 3. There is no COUNTIFS form, because COUNTIFS cannot OR across different columns.

Counting distinct values in older Excel was a classic use:

=SUMPRODUCT(1 / COUNTIF(A2:A6, A2:A6))

For regions North, North, South, South, East this adds 1/2 + 1/2 + 1/2 + 1/2 + 1/1 = 3 distinct regions. It breaks on blanks (a division by zero) and is slow on large data; in Excel 365, =COUNTA(UNIQUE(A2:A6)) is better (dynamic array functions FILTER, SORT and UNIQUE).

Weighted averages

A weighted average is SUMPRODUCT over SUM:

=SUMPRODUCT(C2:C6, D2:D6) / SUM(C2:C6)

The average price weighted by quantity: total revenue 40 + 60 + 32 + 24 + 70 = 226 divided by 45 units, so 5.02. The plain AVERAGE(D2:D6) would give 7.10 and overweight the small Gadget sales. Weighted averages are the right answer for prices, rates and scores any time rows carry different volumes.

Add a condition by multiplying in the test and dividing by the conditional weight total:

=SUMPRODUCT((A2:A6="North")*C2:C6*D2:D6) / SUMPRODUCT((A2:A6="North")*C2:C6)

Multi-column and matrix work

Because it multiplies arrays of any equal shape, SUMPRODUCT handles two dimensional ranges. With a quantity grid in B2:E6 and a price row in B1:E1:

=SUMPRODUCT(B2:E6, B1:E1)

Excel broadcasts the row across the grid (when the dimensions are compatible) and adds the products, giving total revenue across products. For a column of weights against several columns, use MMULT, which fits matrix multiplication better.

For a date range test across a column, combine two conditions: (C2:C6>=DATE(2026,9,1))*(C2:C6<DATE(2026,10,1)). For text matching with wildcards, SUMPRODUCT does not support them; use ISNUMBER(SEARCH("wid", B2:B6)) as the condition instead.

Where it goes wrong

Ranges of different sizes. SUMPRODUCT(A2:A6, B2:B7) is #VALUE!. Keep the same rows in every range.

Text in numeric ranges. With the comma form, text is treated as zero; with multiplication, text raises #VALUE!. A column of numbers stored as text sums to zero in either form (fixing numbers stored as text).

Blank handling. A comparison with an empty cell (A2:A6="") is true for blanks and also for cells with an empty string. Decide which you mean.

Whole columns. SUMPRODUCT((A:A="North")*C:C) evaluates a million rows, and the header row's text turns the multiplication into #VALUE!. Use bounded ranges or an Excel table: SUMPRODUCT((Sales[Region]="North")*Sales[Qty]*Sales[Price]).

Case. The = test is case-insensitive. For case-sensitive matching use EXACT(A2:A6, "North").

Rounding. Floating point results from multiplying decimals may differ in the last digits from a database total. Compare with a tolerance, or round at a defined point (rounding and formatting numbers for finance readers).

Speed

SUMPRODUCT evaluates every row of every range, always, with no early exit and no index. On a few thousand rows nothing is noticeable. On 100,000 rows with a dozen of these formulas, recalculation takes seconds, and with volatile precedents, every keystroke triggers it. Strategies: bound the ranges to the data, put the condition in a helper column once and use SUMIFS on the helper, or move the calculation to a pivot table or the database (slow Excel formulas over many rows). SUMIFS with plain conditions is considerably faster than the equivalent SUMPRODUCT, so use it whenever the condition fits.

When SUMPRODUCT is the wrong formula

It is the wrong formula when SUMIFS, COUNTIFS or AVERAGEIFS can do the job, since those are faster and easier to read for the next person. It is wrong when the formula needs a paragraph of explanation: a three-line SUMPRODUCT with four nested conditions belongs in a query, where CASE, FILTER and joins say what they mean. And it is wrong on data large enough to make the sheet slow. For the formula's reference see Microsoft's SUMPRODUCT page, and for the SQL equivalent of a conditional sum, the PostgreSQL aggregate FILTER clause.