Runs this calculation on the values.
- Left side
(A2:A8="West")- Right side
C2:C8
SUMPRODUCT with conditions explained
This SUMPRODUCT formula turns every West row into 1 and every other row into 0, multiplies that mask by sales, and adds what remains. The live steps make that hidden array visible.
How Excel works this out
Inner to outerMultiply matching entries across arrays and return the total of those products.
- Array
(A2:A8="West")*C2:C8The first array or range whose values you want to multiply and then sum.
Formula sample data
How this SUMPRODUCT formula works
The region test creates a mask, multiplication changes non-West sales to zero, and SUMPRODUCT adds the five West values to return 7,000.
The rows above are ordered from the innermost calculation to the final result. Excel has to finish an inner function before the function around it can use that value. Edit a cell in the grid to see that dependency chain update for real.
Try your own formula and data
The sample spreadsheet is fully editable. Change the formula or any cell it references. The intermediate values recalculate in your browser.
Practice with an exercise
Here is an exercise that uses SUMPRODUCT. Type the formula into a real spreadsheet and we will check your answer as you go.
Revenue waterfall decomposition
IntermediateBreak a revenue change into volume and price effects, a core FP&A variance analysis skill.
Open exerciseCommon mistakes with SUMPRODUCT
If this formula is not doing what you expect, one of these is usually why.
Why does SUMPRODUCT return #VALUE!?
The ranges are different shapes, or text is being multiplied directly. Every range must cover the same rows, and text columns need a comparison such as A2:A8="West" to turn them into TRUE and FALSE first.
Why is my SUMPRODUCT total 0?
The condition matched nothing, so every row contributed zero. Check spelling and trailing spaces in the text being compared.
Why does one row get counted twice?
Check whether the source data contains the same transaction twice. SUMPRODUCT processes each row once; duplicate source rows therefore contribute twice.
Should I use SUMIFS instead?
SUMIFS is easier to read for straightforward criteria. SUMPRODUCT earns its place when you need visible array arithmetic, weighted values, or conditions SUMIFS cannot express.