SUMPRODUCT Explained: Multiply Down, Then Add

1. What it does, in one line

Before the explanation: you have a Units column and a UnitPrice column and no Revenue column. Say how you would get total revenue without adding one.

=SUMPRODUCT(Orders[Units], Orders[UnitPrice])      9890
=SUM(Orders[Revenue])                              9890

Identical. SUMPRODUCT did what the Revenue column does, in one cell, without the column existing. Row one is 4 × 220 = 880, row two is 10 × 85 = 850, and so on down all sixteen rows, and then it adds the sixteen products together.

The part worth internalising is multiply first, add second. Multiplying the two column totals would give 101 × 2,125, which is a number with no meaning at all. The pairing is per row, and that is the whole function.

It also takes more than two ranges. SUMPRODUCT(a, b, c) multiplies three values per row. That matters in a moment, because a condition is just another range of numbers.

2. The number it exists for: a weighted average

Before the explanation: the UnitPrice column holds sixteen prices. Somebody averages it and reports the average price. Say whether that number describes the business.

=AVERAGE(Orders[UnitPrice])                                       132.81
=SUMPRODUCT(Orders[Units], Orders[UnitPrice]) / SUM(Orders[Units])  97.92

A gap of nearly 35. Both are arithmetically correct and they answer different questions.

AVERAGE treats each of the sixteen orders as one thing: seven desk orders at 220, five chair orders at 85, four lamp orders at 40, averaged as sixteen equal items. The weighted version treats each of the 101 units as one thing, which is what "average price" means to anyone reading a sales report. 9,890 of revenue over 101 units is 97.92 per unit, and you can check it: 9,890 ÷ 101 = 97.92.

The reason the simple average runs high here is that the expensive product sells in small orders and the cheap one sells in large ones. Two lamp orders of 12 and 15 units count as two items in an unweighted average, exactly like a single desk order of 2 units counts as one. Weighted, those same lamp orders carry 27 units against the desk order's 2.

This is the single most valuable thing this function does, and the pattern is always the same shape:

=SUMPRODUCT(weights, values) / SUM(weights)

Units and price. Headcount and salary. Order value and discount rate. Population and per-capita anything. Whenever you are averaging a rate, a price or a percentage, there is a weight column somewhere, and leaving it out is the commonest quiet error in business reporting.

Picture the last average you put in front of somebody. What was the weight column, and was it in the formula?

3. Conditions are ones and zeros

A comparison like Region="North" does not produce a filter, it produces a column of TRUE and FALSE, one per row. Multiply that column by another and TRUE behaves as 1 while FALSE behaves as 0, so every row that fails the test contributes zero.

=SUMPRODUCT((Orders[Region]="North") * Orders[Revenue])       2495

North's revenue, with no helper column and no SUMIF. Every non-North row was multiplied by 0 and vanished from the sum.

Stack conditions the same way. Multiplying is AND, adding is OR, exactly as it is in a FILTER formula, because it is the same arithmetic.

=SUMPRODUCT((Orders[Region]="East") * (Orders[Revenue]>600) * Orders[Revenue])
2440

=SUMPRODUCT(((Orders[Region]="East") + (Orders[Region]="West")) * Orders[Revenue])
4725

Check the second one: East is 3,040 and West is 1,685, and 3,040 + 1,685 = 4,725. The brackets around the added pair are not optional, and forgetting them is the most common mistake in an OR condition.

One warning about OR built with addition: if a row can satisfy both conditions, its flag becomes 2 and it is counted twice. A region cannot be both East and West, so it is safe here. "Revenue over 500 or units over 5" is not safe, and the fix is to compare against zero: --(((a)+(b))>0).

4. Counting with the double minus

To count rather than total, you want the flags themselves added up. But SUMPRODUCT ignores logical values when they arrive on their own, so the flags need converting to numbers first, and the standard way is two minus signs.

=SUMPRODUCT(--(Orders[Revenue]>600))                    8
=SUMPRODUCT(--(Orders[Units]>5), --(Orders[UnitPrice]<100))   8

The double minus, usually called the double unary, is not a trick as much as arithmetic done twice. The first minus turns TRUE into −1, the second turns −1 into 1, and the same happens to FALSE and 0. Multiplying by 1 would work identically, and so would adding 0; the minus signs are just the shortest way to write it.

Eight orders are over 600. Eight orders also have both more than five units and a price under 100, which is a two-condition count with no helper column and no COUNTIFS.

Mixing the two forms is fine and worth knowing: a flag argument makes a count, and putting a value range alongside it makes a conditional total.

=SUMPRODUCT(--(Orders[Revenue]>600), Orders[Revenue])     6475

5. Comparing two columns row by row

This is the job SUMIFS genuinely cannot do, because its criteria compare a column to a fixed value rather than to another column.

=SUMPRODUCT(--(Orders[Revenue] > Orders[UnitPrice] * 3))     12

Twelve orders are worth more than three times their unit price, which is another way of saying twelve orders were for more than three units. The comparison happens once per row, against that row's own values.

The same shape answers the questions that actually come up: how many invoices were paid after their due date, --(PaidDate > DueDate); how many line items were below cost, --(Price < Cost); how many months missed target, --(Actual < Target). Every one of them is a two-column comparison and none of them is a SUMIFS.

6. Where it still beats SUMIFS

SUMIFS arrived long after SUMPRODUCT and took over most of the day-to-day work. It is easier to read, it is faster on large ranges, and for a plain conditional total it is the right answer. Its own guide covers that case properly.

Four situations still belong here.

Row-by-row comparisons, as in section five.

A condition that has to be calculated. SUMIFS cannot apply a function to the range it is testing, so grouping by month means adding a month column. SUMPRODUCT can compute it in place:

=SUMPRODUCT(--(MONTH(Orders[OrderDate])=3), Orders[Revenue])     2585

A condition that comes from a lookup. The orders table has no category column; category lives in the products table. SUMPRODUCT can do the lookup for every row inside the condition:

=SUMPRODUCT((INDEX(Products[Category],
             MATCH(Orders[Product], Products[Product], 0)) = "Lighting")
            * Orders[Revenue])
1600

same shape, "Furniture"                                          8290

1,600 and 8,290 add to 9,890, so every order was categorised. That is a cross-table conditional total in one cell, with no column added to either table, and it composes with the lookup pattern you already know.

Weighted arithmetic of any kind, which is section two and the reason the function is worth learning at all. The same shape produces cost of goods from a price list:

=SUMPRODUCT(Orders[Units],
            INDEX(Products[Cost], MATCH(Orders[Product], Products[Product], 0)))
6076

It is also the engine behind any scored model, where a set of weights meets a set of measures. A risk index that colours itself is that pattern built out in full.

7. The two ways it breaks

Before the explanation: one of your ranges is sixteen rows and the other is fifteen. Predict what happens.

=SUMPRODUCT(Orders[Units], G2:G16)      #VALUE!

Different sizes error. There is no partial pairing and no silent truncation, which is the best possible behaviour: the mismatch is exactly the kind of thing that would produce a plausible wrong total in a less strict function. When you see this, count the rows in each argument.

Text behaves differently in the two forms, and this one genuinely surprises people. With commas between the ranges, text is treated as zero and the formula returns an answer. With asterisks, text cannot be multiplied and the formula errors.

=SUMPRODUCT(Orders[Units], Orders[Rep])        0
=SUMPRODUCT(Orders[Units] * Orders[Rep])       #VALUE!

The comma form's tolerance sounds friendlier and is more dangerous. A column that is mostly numbers with a few text entries returns a total that is short by exactly those rows, with no error, which is the same failure as numbers stored as text wearing a different hat. If the total looks slightly low, check the types before checking the formula.

The practical rule: use commas when every argument is genuinely numeric, and use asterisks when conditions are involved, which is when you want the strictness anyway.

8. When not to use it

Three cases, said plainly, because a page about a function should also say where it stops.

A plain conditional total. SUMIFS is clearer to read and quicker to check. Reach for SUMPRODUCT when SUMIFS cannot express the condition, not before.

Whole-column references on a big sheet. SUMPRODUCT evaluates every row you give it, so A:A means a million rows per condition, per formula. On a large model this is the classic cause of a workbook that recalculates for several seconds after every keystroke. Point it at the table column.

When you need to see the rows. SUMPRODUCT returns one number and shows nothing about which rows contributed. If the question is "which orders", that is a filter or a FILTER formula, not this.

Edge cases that catch people

Six worth knowing.

Blank cells. Treated as zero, so a blank in the weights column removes that row from a weighted average silently. Count the blanks before dividing.

OR conditions that overlap. Adding two flags gives 2 for a row matching both, which double-counts it. Wrap in --(( ... )>0) whenever both can be true.

Missing brackets around a condition. --Orders[Revenue]>600 is not the same as --(Orders[Revenue]>600), and the first one produces something confidently wrong rather than an error.

Errors anywhere in the range. One #N/A in a column makes the whole SUMPRODUCT an error. That is correct behaviour, and hiding it turns a visible problem into a quiet undercount.

Ranges that start on different rows. SUMPRODUCT(A2:A17, B3:B18) is the same size and pairs every row with the wrong partner. It gives a clean, plausible, entirely wrong number. Point both at named table columns and the misalignment becomes impossible.

Volatile functions inside it. A SUMPRODUCT containing something that recalculates constantly, such as a function that reads today's date, re-evaluates every row on every change. Compute the volatile part once in its own cell and reference it.