SUMPRODUCT in Excel: what it does and when to use it

SUMPRODUCT multiplies the entries that sit in the same position across two or more ranges, then returns the sum of those products. The classic use is a weighted total in one cell: point =SUMPRODUCT(B2:B4, C2:C4) at a quantity column and a price column and it returns the order total with no helper column of line amounts. Its second job, and the reason it turns up in so many inherited workbooks, is conditional totals: comparisons written inside SUMPRODUCT can sum with AND logic, OR logic, and criteria that SUMIFS cannot express.

This page covers the syntax, the two documented behaviors that explain most SUMPRODUCT trouble, the conditional patterns behind “SUMPRODUCT IF”, and the failure modes: the #VALUE! from mismatched ranges and the silently short total from numbers stored as text.

What does SUMPRODUCT do?

The syntax is =SUMPRODUCT(array1, [array2], ...). The first array is required; arrays 2 through 255 are optional. Give it two ranges of the same shape and it multiplies them position by position, then adds everything up.

Microsoft’s walkthrough of its own grocery example makes the equivalence explicit: written out by hand for the grid above, the same total is =B2*C2+B3*C3+B4*C4, and the result is the same. SUMPRODUCT is that longhand, compressed into one term that survives rows being added.

The pattern earns its keep in weighted calculations. With weights 50, 30, and 20 in B2:B4 and scores 84, 90, and 70 in C2:C4:

multiplies each score by its weight (4,200 + 2,700 + 1,400 = 8,300) and divides by the total weight (100), for a weighted average of 83. The division at the end is ordinary arithmetic applied to SUMPRODUCT’s result; nothing special is required.

Multiplication is only the default operation. Per Microsoft, addition, subtraction, and division are also possible: replace the commas between the arrays with the operator you want, and use parentheses to control the order of operations.

The two rules behind most SUMPRODUCT problems

Two sentences from Microsoft’s documentation do more troubleshooting work than anything else on the function’s page.

First: “SUMPRODUCT treats non-numeric array entries as if they were zeros.” Text in a range does not stop the calculation and does not raise an error; it contributes 0 and the sum moves on. That is convenient when a range genuinely contains labels, and dangerous when it contains numbers stored as text, which are, to SUMPRODUCT, non-numeric. More on that failure below.

Second: “The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.” Microsoft’s own example is =SUMPRODUCT(C2:C10,D2:D5): the ranges aren’t the same size, so instead of a number you get the error. Unlike the zeros rule, this one is loud, and loud is good: a range that got clipped during an edit announces itself instead of shipping a wrong total.

Keep both rules in mind and the rest of this page is largely corollaries.

How do I use SUMPRODUCT with a condition?

Despite the phrase people search for, there is no IF function involved. You write the test directly against a range. A comparison like A2:A7="East" produces one TRUE or FALSE per row, and TRUE and FALSE are not numbers, so under the zeros rule SUMPRODUCT would count them as nothing. Arithmetic converts them: TRUE*1=1 and FALSE*1=0, the identity Microsoft’s array-formula guidelines lean on. Multiplying the comparison by the value column therefore does both jobs at once: the tests become 1s and 0s, and each 1 picks up its row’s value.

Skip the coercion and you get the most common SUMPRODUCT surprise:

The same formulas circulate in a second spelling: =SUMPRODUCT(--(A2:A7="East"), B2:B7), with two minus signs in front of the comparison and the ranges separated by a comma again. The double unary negates each value twice, and that arithmetic converts TRUE and FALSE to 1 and 0 just as multiplying by 1 does; the same -- operator appears on Microsoft’s TEXT-function page as a way to convert text to numbers. Both spellings return 415 here. Microsoft’s own SUMPRODUCT examples use the multiplication form.

Coerce a lone comparison and you have a count instead of a sum: =SUMPRODUCT(--(A2:A7="East")) returns 3. For plain conditional counting, though, COUNTIF and COUNTIFS are the purpose-built tools.

How do I use SUMPRODUCT with multiple criteria?

Multiply more comparisons in. Each contributes a 1 or a 0, and a product of tests is AND logic: a row survives only if every test on it passes.

Microsoft’s version of this pattern, Example 3 on the function’s page, is =SUMPRODUCT((B2:B9=B12)*(C2:C9=C12)*D2:D9), with the criteria kept in cells (B12 and C12) rather than typed into the formula, which makes them easy to change without editing the formula itself.

Swap the multiplication between two tests for addition and you have OR logic:

Against the table above, the East rows contribute 500 + 410 + 275 and the North row adds 220: 1,405. One caution about the addition trick: if both tests can be true on the same row, that row’s 1 + 1 = 2 counts it twice. Two regions cannot overlap, so this example is safe; if the tests can overlap, either make them mutually exclusive or subtract the overlap.

SUMPRODUCT or SUMIFS: which one?

For the East-and-Apples total above, SUMIFS produces the same 775 with less machinery: =SUMIFS(C2:C7, A2:A7, "East", B2:B7, "Apples"). It is purpose-built for the job: Microsoft describes SUMIFS as adding all of its arguments that meet multiple criteria, it accepts up to 127 range and criteria pairs, and its criteria support the ? and * wildcards. The labeled pairs also read as documentation: range, then the test applied to it, with the sum range first. The full treatment of that family, including its own failure modes, is in the COUNTIF and SUMIF guide.

Reach for SUMPRODUCT when the condition will not fit SUMIFS’s shape:

• OR logic. SUMIFS adds only the rows that meet every one of its criteria. Either-or across different columns is the addition pattern above.
• Arithmetic on the values, inline. Microsoft’s Example 2 sums sales plus expenses per agent in one formula, =SUMPRODUCT(((Table1[Sales])+(Table1[Expenses]))*(Table1[Agent]=B8)). SUMIFS sums one range as it stands; combining columns first would take a helper column.
• Tests computed from the data. A SUMPRODUCT comparison can test any expression, such as a calculation on a date or text column, rather than the column’s stored values. SUMIFS compares its criteria against the criteria range as it is.

The tiebreaker is the next person who opens the workbook. A SUMIFS states its criteria in labeled pairs; a SUMPRODUCT hides them inside arithmetic. If SUMIFS can express the condition, use SUMIFS.

Why is my SUMPRODUCT 0, wrong, or slow?

Four failure modes cover most broken SUMPRODUCTs, and the two worst make no sound.

1. It returns #VALUE!

The usual cause: the arrays are different sizes, one range covering six rows and another seven, often after rows were inserted at the edge of a range or one reference was edited and the others were not. Recheck that every range in the formula spans the same rows and columns. With the multiplication spelling, text inside a multiplied range produces the same error, because text cannot be multiplied. The rest of Excel’s error vocabulary is in every Excel error explained.

2. It returns 0 with nothing visibly wrong

A comparison was left raw, as in the amber example above: TRUE and FALSE count as zeros until arithmetic converts them. Multiply the test by the value range, or coerce it with --( ).

3. The total is short, and looks fine

Numbers stored as text are non-numeric, so SUMPRODUCT counts them as zeros, silently. A column where a third of the entries were pasted in as text sums to two thirds of the truth, with no error anywhere on the sheet. How those cells get into a workbook, how to spot the green triangles, and how to convert them is covered in numbers stored as text in Excel.

4. It calculates slowly

Microsoft’s performance note on the function’s page warns against full-column references: =SUMPRODUCT(A:A,B:B) multiplies all 1,048,576 cells of column A by all 1,048,576 cells of column B before adding them. Point the ranges at the rows you actually use.

SUMPRODUCT and dynamic arrays

The pairwise arithmetic has a second spelling with SUM: Microsoft’s array-formula guidelines use =SUM(C2:C11*D2:D11) for the same multiply-then-total job. For years the practical distinction was how you entered it: legacy array formulas had to be confirmed with Ctrl+Shift+Enter, while SUMPRODUCT’s instructions have always ended with a plain press of Enter. In current Excel, dynamic array formulas are confirmed with Enter too, so that historical entry advantage is gone; for numeric data the two spellings do the same job. On ranges containing text they part ways: SUMPRODUCT counts the text as zeros, while the multiplication inside SUM returns the #VALUE! error, because text cannot be multiplied. Which spelling a workbook uses often just dates it. The eras, spilling, and where the keystroke still matters are covered in array formulas in Excel.

The short version

• SUMPRODUCT multiplies corresponding entries across ranges and adds the products: =SUMPRODUCT(B2:B4, C2:C4) is a one-cell weighted total, and /SUM(weights) on the end makes it a weighted average.
• Two documented rules explain most trouble: non-numeric entries count as zeros (silent), and arrays of different dimensions return #VALUE! (loud).
• Conditions are comparisons coerced by arithmetic: =SUMPRODUCT((A2:A7="East")*B2:B7) sums the East rows, because TRUE*1=1 and FALSE*1=0. Multiplying tests is AND; adding them is OR; a raw, uncoerced comparison returns 0.
• Prefer SUMIFS when it can state the condition; keep SUMPRODUCT for OR logic, inline arithmetic, and computed tests.
• The quiet one is numbers stored as text, which SUMPRODUCT counts as zeros. And when a SUMPRODUCT total moves after someone edits the workbook, compare the two versions and confirm the number moved because the data did, not because a range slipped or a column turned to text.