SUMPRODUCT is one of Excel's most versatile functions. At its simplest it multiplies ranges together and sums the result, but its real power is doing conditional calculations that would otherwise need complex formulas. It's worth learning well.
The basic behavior
SUMPRODUCT multiplies corresponding items in arrays, then adds up the products:
=SUMPRODUCT(array1, array2, ...)
Example — you have quantities in B2:B10 and prices in C2:C10, and want the total value:
=SUMPRODUCT(B2:B10, C2:C10)
This multiplies each quantity by its price and sums everything in one step — no helper column needed. That alone is useful for invoice totals, weighted calculations, and the like.
The powerful part: conditions
SUMPRODUCT can do conditional counting and summing by using logical expressions that evaluate to arrays of TRUE/FALSE (which become 1/0 when multiplied).
Count rows meeting multiple conditions:
=SUMPRODUCT((A2:A100="East")*(B2:B100>100))
Each condition produces an array of TRUE/FALSE. Multiplying them gives 1 only where both are true, and SUMPRODUCT adds those up — giving you the count of rows where region is East AND sales exceed 100. This is like COUNTIFS, but more flexible.
Sum with conditions:
=SUMPRODUCT((A2:A100="East")*(B2:B100))
This sums column B only where column A is "East" — like SUMIF, but extensible to conditions SUMIF can't handle.
Why use it over SUMIFS/COUNTIFS?
For straightforward conditions, SUMIFS and COUNTIFS are simpler and you should use them. SUMPRODUCT shines when you need something they can't do easily:
- Conditions involving calculations, like counting where the product of two columns exceeds a threshold.
- OR logic across different columns (harder with COUNTIFS, which is AND-based).
- Working in older Excel versions where you need array-style math without pressing Ctrl+Shift+Enter (SUMPRODUCT handles arrays natively).
The mechanics to understand
The trick is that comparisons like (A2:A100="East") produce an array of TRUE/FALSE values. Multiplying arrays together (or by 1) converts them to 1s and 0s. SUMPRODUCT then sums the result. Once you grasp that TRUE×TRUE=1 and anything×FALSE=0, the conditional patterns make sense.
Tip: use * between conditions for AND logic, and + for OR logic. Wrap the whole thing carefully with parentheses around each condition.
A caution
SUMPRODUCT processes entire arrays, so referencing whole columns (A:A) can be slow on large sheets. Reference only the range you need (A2:A100) for good performance.
The takeaway
SUMPRODUCT multiplies and sums arrays, which makes it a Swiss-army knife for weighted totals and multi-condition math. Reach for SUMIFS/COUNTIFS first for simple cases, but when you need conditions they can't express, SUMPRODUCT is the powerful fallback that handles almost anything.