SUMPRODUCT: Sum Values Based on Several Conditions

SUMPRODUCT multiplies arrays element by element and adds up the results. (If all you need is a plain column total, AutoSum writes the SUM formula for you.) That one behaviour makes it a conditional-sum tool, a weighted-average tool and a two-way lookup tool, which is why it survives in serious models long after SUMIFS arrived.

The basic job

With quantities in B2:B100 and unit prices in C2:C100, total revenue is:

=SUMPRODUCT(B2:B100, C2:C100)

No helper column of row totals, and no array-entry gymnastics.

Adding conditions

A comparison such as A2:A100="North" produces an array of TRUE and FALSE. Multiplying arrays together coerces those to 1 and 0, so anything that fails a test contributes zero:

=SUMPRODUCT((A2:A100="North")*(B2:B100), C2:C100)

Two conditions, combined with * for AND:

=SUMPRODUCT((A2:A100="North")*(D2:D100="Q1")*B2:B100*C2:C100)

Use + instead of * for OR. To count rather than sum, drop the value array: =SUMPRODUCT((A2:A100="North")*1).

When to use it instead of SUMIFS

For a straightforward “sum column C where column A is North”, SUMIFS is faster to read, faster to calculate and easier for the next person to maintain. Reach for SUMPRODUCT when SUMIFS cannot express the question:

• the criterion is itself a calculation — (YEAR(A2:A100)=2026), or (B2:B100>AVERAGE(B2:B100));
• you need to multiply two columns before summing, as in the revenue example;
• you want a weighted average: =SUMPRODUCT(values, weights)/SUM(weights).

Two things that break it

• Mismatched range sizes. Every array must have the same dimensions or SUMPRODUCT returns #VALUE!. Avoid whole-column references such as A:A mixed with B2:B100.
• Text or errors inside the range. A single #N/A in a referenced range propagates to the result. Clean the data, or wrap the offending array in IFERROR.

SUMPRODUCT recalculates over every row you give it, so on very large ranges prefer SUMIFS where it will do the job.

Why no Ctrl+Shift+Enter

SUMPRODUCT does array maths, yet it never needed the array-entry keystroke that older formulas required. That is deliberate: the function was built to accept arrays as ordinary arguments, so Excel evaluates them without being told to.

For years that made it the polite way to write array logic in a shared workbook — a colleague could edit the formula and press Enter without silently breaking it. The reasoning behind boolean multiplication, and what changed when dynamic arrays arrived, is covered in array formulas in Excel.

Two-way lookup

The use that surprises people: retrieving one value from a grid by matching both a row label and a column label.

=SUMPRODUCT((A2:A20=G1)*(B1:E1=G2)*B2:E20)

Row labels in A, column headers in row 1, values in the block between. The two comparisons produce a vertical array and a horizontal one; multiplied together they yield a grid of zeros with a single 1 at the intersection, which isolates exactly one value from the data block.

It only works when the combination is unique. If two rows carry the same label, SUMPRODUCT adds their values together and returns a total rather than an error — a wrong answer that looks entirely plausible. INDEX/MATCH on multiple criteria is the safer choice when uniqueness is not guaranteed.

Counting distinct values

A classic that predates UNIQUE and still works everywhere:

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

Each value contributes 1 divided by how often it occurs, so three copies of “North” contribute one third each and total exactly 1. The sum is the number of distinct entries.

One blank cell in the range breaks it with a division-by-zero error, since COUNTIF returns 0 for the blank. Guard against it:

=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100, A2:A100&""))

The SUMIFS family appears in the MOS Excel Expert objectives; see the MO-211 exam guide.