How to Use SUMPRODUCT in Excel: Multiply, Add and Sum With Conditions

Microsoft describes it as returning the sum of the products of corresponding ranges or arrays. Multiplication is the default, but it can add, subtract and divide too. The syntax is =SUMPRODUCT(array1, array2, array3, ...), and only the first array is required. You can include up to 255 arrays.

Example: price x quantity in one cell

Say column C holds prices and column D holds quantities for four items, in rows 2 to 5. Without SUMPRODUCT, you'd write it out longhand:

=C2*D2+C3*D3+C4*D4+C5*D5

With it, you write =SUMPRODUCT(C2:C5,D2:D5). Microsoft's example gives the same result either way, $78.97, but the short version doesn't get longer when the list does. That's the whole point of the function: one formula replaces a chain of multiplications that's easy to mistype.

How to sum with conditions

SUMPRODUCT gets more interesting when you put a test inside it. Microsoft's Example 3 asks how many cherries the East region sold:

=SUMPRODUCT((B2:B9=B12)*(C2:C9=C12)*D2:D9)

The first bracket checks each row's region against the value in B12, the second checks each row's product against C12, and the third range holds the quantities. Rows that pass both tests count, and everything else becomes zero. Microsoft's intro example works the same way: it matches every row for Item Y in Size M and adds them up, so 21 plus 41 equals 62.

Microsoft's Example 2 does the same inside an Excel table, using structured references to the Sales, Expenses and Agent columns. It adds each row's sales and expenses, then multiplies by a test that checks whether the agent matches the name in B8, which gives total net sales for one agent.

The catch: TRUE and FALSE count as zero

Here's where people get a result of zero and can't work out why. CFI explains that a logical test, such as C4:C12="WEST", produces a list of TRUE and FALSE values, and SUMPRODUCT treats those as zeros if you pass them in as a separate argument. So you get nothing.

The fix is to turn them into numbers. CFI uses a double negative, two minus signs in front of the test, which forces TRUE to 1 and FALSE to 0:

=SUMPRODUCT(--(C4:C12="WEST"),D4:D12)

Multiplying the tests together, as in Microsoft's cherries example, does the same job, because multiplication converts TRUE and FALSE to 1 and 0 as well. CFI's advice is that it's usually wise to convert logical results to 1s and 0s either way.

Two more rules from Microsoft

• Every range must be the same size. =SUMPRODUCT(C2:C10,D2:D5) returns #VALUE! because one range has nine rows and the other has four. Microsoft also says non-numeric entries are treated as zeros.
• Don't use whole columns. Microsoft warns that =SUMPRODUCT(A:A,B:B) would multiply 1,048,576 cells by 1,048,576 cells before adding them, which slows the workbook right down. Use the rows you need.

For operations other than multiplication, Microsoft says to replace the commas with the arithmetic operators you want, and to put each array in brackets so Excel does them in the right order.

A good companion to this page is the Excel Formulas for Beginners guide.

SUMPRODUCT for a weighted average

CFI calls the weighted average one of the most common uses. With values in C2:C8 and their weights in D2:D8, SUMPRODUCT of the two ranges gives you the weighted total. Dividing that by the sum of the weights is standard arithmetic, so the pattern is =SUMPRODUCT(C2:C8,D2:D8)/SUM(D2:D8). Weighted averages deserve a guide of their own, so I'll keep it at that here.

For filtered lists there is a function built for the job, explained in SUBTOTAL in Excel.

Other things SUMPRODUCT can do

CFI says SUMPRODUCT can use the results of other functions directly, commonly LEN, ISBLANK, ISTEXT and VLOOKUP. It lists two-way lookups, counting characters or words, counting distinct values, counting duplicates and unique values, and summing by month with MONTH. If you only need a simple lookup, VLOOKUP is easier to read.

SUMPRODUCT patterns at a glance

Task	Pattern	Source
Total of price x quantity	=SUMPRODUCT(C2:C5,D2:D5)	Microsoft Example 1: $78.97
Sum with two conditions	=SUMPRODUCT((B2:B9=B12)*(C2:C9=C12)*D2:D9)	Microsoft Example 3
Sum with one condition	=SUMPRODUCT(--(C4:C12="WEST"),D4:D12)	CFI Example 1 (double negative)
Weighted average	SUMPRODUCT(values, weights) / SUM(weights)	CFI Example 2, plus standard arithmetic

Step by step

1. Check that every range has the same number of rows.
2. List the arrays to multiply, separated by commas.
3. For a condition, wrap it in brackets and multiply, or use the double negative.
4. Avoid whole-column references.
5. For a weighted average, divide by the SUM of the weights.

My verdict

One function replaces a long chain of multiplications, but only if the ranges match. Once you're comfortable, SUMPRODUCT handles conditions that would otherwise need a helper column. If you only have one or two simple conditions, though, SUMIF and COUNTIF are easier for the next person to read. Use SUMPRODUCT when they can't do the job.

Frequently asked questions

What does SUMPRODUCT do in Excel?

It multiplies the matching cells in two or more ranges and adds the results. Microsoft's grocery example totals prices x quantities to $78.97.

Why does my SUMPRODUCT return zero?

Usually because a logical test is producing TRUE and FALSE, which SUMPRODUCT treats as zeros. CFI's fix is a double negative to turn them into 1s and 0s.

Why does SUMPRODUCT return #VALUE!?

Microsoft says the arrays must have the same dimensions. Ranges of different sizes, such as C2:C10 and D2:D5, return #VALUE!.

Can SUMPRODUCT use multiple criteria?

Yes. Microsoft multiplies two tests together with the quantity range, as in its example counting cherries sold in the East region.

How many arrays can SUMPRODUCT take?

Up to 255, according to Microsoft. Only the first array is required.