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 top row above is the whole formula. Open any argument that holds a calculation of
its own to see the values it used. Excel works out those inner parts first, because the
function around them needs their results. Edit a cell in the grid to see every value
update.
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.
Common 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.