Sum with Conditions and Math Using SUMPRODUCT

Excel & Google Sheets

How it works

The comparison (A2:A10="Sales") produces one TRUE or FALSE per row, and multiplying by the values converts TRUE to 1 and FALSE to 0 — so non-matching rows contribute nothing. SUMPRODUCT then adds it all up: the total of column B for Sales rows only. This is the pre-SUMIFS workhorse, and it still does what SUMIFS can't: OR logic by adding conditions — ((A2:A10="Sales")+(A2:A10="East"))*B2:B10 — and calculations inside the sum, like units times price filtered by region, all in one cell. It works identically in every Excel version and in Google Sheets.

(A2:A10="Sales")

One TRUE/FALSE per row — TRUE where the region is Sales.

*(B2:B10)

Multiplying turns TRUE/FALSE into 1/0 and applies it to each value, zeroing out non-matching rows.

SUMPRODUCT(…)

Adds the surviving values into a single total.

When to use it

Use it for OR-logic totals (Sales or East), sums that need per-row math (units × price by product line), computed conditions like month-of-date tests, or workbooks that must run on any Excel version.

Common mistakes

• ✕Separating a condition with a comma instead of *.

  ✓=SUMPRODUCT((A2:A10="Sales"),B2:B10) returns 0 — the comma form doesn't convert TRUE/FALSE to numbers. Multiply the arrays, or coerce with a double minus: --(A2:A10="Sales").

• ✕Ranges of different sizes.

  ✓=SUMPRODUCT((A2:A10="Sales")*(B2:B9)) returns #VALUE!. Every array must cover exactly the same rows.

• ✕Text sitting in the value column.

  ✓A cell holding "pending" makes the multiplication fail with #VALUE!. Clean the column, or check it with ISNUMBER first.

• ✕Reaching for SUMPRODUCT when SUMIFS is enough.

  ✓For plain AND conditions, SUMIFS is faster and easier to read. Keep SUMPRODUCT for OR logic and in-formula math.