SUMPRODUCT Function

Excel Guides / Math & Statistics

Math & Statistics

Multiply columns row by row and add up the results, with conditions that SUMIFS cannot express.

Updated 12 Oct 2026 · 5 min read · By Sky Dashboards

SUMPRODUCT multiplies matching items in two or more ranges and adds up the results. That alone covers weighted totals like units times price. Add conditions as TRUE and FALSE arrays and it becomes a flexible conditional sum that handles cases SUMIFS cannot, in every version of Excel and without Ctrl + Shift + Enter.

SUMPRODUCT syntax

SUMPRODUCT multiplies the first item of each array together, then the second items, and so on, and finally adds those products up. Given a single array, it simply adds it up.

• Arrays must match in size. C2:C6 and D2:D7 return #VALUE!.
• Text and logical values count as zero. This matters as soon as you add conditions, as shown below.

Weighted totals: units times price

A ceramics studio sells five products. Units sold are in column C and prices in column D. Revenue is units times price on each row, added up:

The alternative is a Revenue column with =C2*D2 filled down and a SUM underneath. Both work. SUMPRODUCT keeps the sheet smaller when only the total matters.

Weighted average

The plain average of the five prices is $38.80, but that treats a vase that sold 12 times the same as a mug that sold 42 times. A weighted average divides the weighted total by the sum of the weights:

The same pattern works for any weighting: margin weighted by sales, a coach's session rating weighted by attendance, delivery time weighted by order count.

Conditional sums with boolean arrays

A comparison such as B2:B6="Kitchen" returns one TRUE or FALSE per row: {TRUE;TRUE;FALSE;FALSE;TRUE}. To use it as a filter, turn it into 1s and 0s and multiply it in.

The double minus is called the double unary. The first minus turns TRUE into -1 and FALSE into 0, the second turns -1 back into 1. Rows that fail the test are multiplied by 0 and drop out.

The other common style multiplies everything inside one argument. Arithmetic turns TRUE and FALSE into numbers by itself, so no -- is needed:

Each extra condition is another bracket multiplied in. Multiplying is AND: a row survives only if every bracket is 1.

Why it works without Ctrl+Shift+Enter

In older versions of Excel, a formula like =SUM((B2:B6="Kitchen")*C2:C6*D2:D6) only worked when confirmed with Ctrl + Shift + Enter, which turned it into an array formula wrapped in braces. With a plain Enter it returned #VALUE! or a wrong answer. SUMPRODUCT was built to take arrays, so it evaluates them properly with a plain Enter. That is why it became the standard tool for conditional maths before SUMIFS and dynamic arrays existed.

In Microsoft 365 and Excel 2021 or later, every formula handles arrays natively, so the SUM version works with Enter too. SUMPRODUCT is still worth knowing because it behaves the same in every version, which matters when you share files with people on older Excel.

OR conditions with +

Adding conditions gives OR logic. (B2:B6="Kitchen")+(B2:B6="Decor") is 1 when either test is true. Within one column a row can only match one option, so the result is always 0 or 1.

Across different columns, a row can pass both tests and score 2, which would double its value. Wrap the addition in >0 to turn it back into a plain yes or no:

Performance: avoid full-column references

SUMPRODUCT calculates every cell in every array you give it. Point it at whole columns such as C:C and each array has 1,048,576 rows, almost all of them empty. A handful of these formulas can make a workbook noticeably slow, because they recalculate whenever their inputs change.

• Use exact ranges, such as C2:C500, with some room to grow.
• Better, turn the data into an Excel Table and use structured references like Sales[Units], which grow with the data.
• Keep the work inside simple. Every function on a range, such as MONTH(A2:A500), runs once per row.

SUMPRODUCT or SUMIFS?

SUMIFS only accepts real ranges as criteria ranges, so a condition such as "the month is March, in any year" needs SUMPRODUCT or a helper column:

For everything SUMIFS can do, it is faster and easier to read. Reach for SUMPRODUCT when SUMIFS runs out of road.

Frequently asked questions

What does -- mean in SUMPRODUCT?

It is two minus signs, called the double unary. It converts TRUE and FALSE into 1 and 0 so SUMPRODUCT can multiply them. Without it, SUMPRODUCT treats TRUE and FALSE as zero.

Does SUMPRODUCT need Ctrl+Shift+Enter?

No. SUMPRODUCT handles arrays natively in every version of Excel, so a plain Enter is enough.

Why does SUMPRODUCT return #VALUE!?

Either the arrays are different sizes, or a value being multiplied with * is text, such as n/a in a number column. Make every range cover the same rows, or pass the number ranges as separate arguments so text is treated as zero.

Is SUMPRODUCT slower than SUMIFS?

Usually, because it evaluates every cell in its ranges. On small, exact ranges you will not notice. On whole columns or across thousands of formulas, you will.

Related guides

All 32 Excel guides