SUMPRODUCT with multiple conditions
SUMPRODUCT can add up a column for rows that meet several conditions by multiplying TRUE/FALSE tests together, and unlike SUMIFS it also handles OR logic.
=SUMPRODUCT(($B$2:$B$500="West")*($C$2:$C$500="Widget")*$D$2:$D$500)The sheet: Sales sheet: column B is Region, column C is Product, column D is Revenue. You want the revenue for Widget orders in the West region.
How it works, step by step
- 01($B$2:$B$500="West") returns TRUE for each West row and FALSE for the rest.
- 02($C$2:$C$500="Widget") does the same for the product column.
- 03Multiplying the tests together with the Revenue column turns TRUE into 1 and FALSE into 0, so each row contributes its revenue only when both tests pass.
- 04SUMPRODUCT adds up the results into a single number.
The same logic in four tools
=SUMPRODUCT(($B$2:$B$500="West")*($C$2:$C$500="Widget")*$D$2:$D$500)=SUMPRODUCT(($B$2:$B$500="West")*($C$2:$C$500="Widget")*$D$2:$D$500)SELECT COALESCE(SUM(revenue), 0) AS total_revenue
FROM sales
WHERE region = 'West'
AND product = 'Widget';import pandas as pd
mask = (sales["region"] == "West") & (sales["product"] == "Widget")
total = sales.loc[mask, "revenue"].sum()Mistakes that break it
- !Text or an error in the Revenue column makes the multiplication return #VALUE!. The comma form, =SUMPRODUCT(--(B2:B500="West"),--(C2:C500="Widget"),D2:D500), treats text in the sum column as zero.
- !All ranges must be exactly the same size. $B$2:$B$500 with $D$2:$D$499 returns #VALUE!.
- !Whole-column references like B:B make SUMPRODUCT calculate over a million rows and slow the workbook. Use a bounded range.
- !The = test in Excel ignores upper and lower case, but pandas == and SQL = on most databases do not. West and west match in the sheet and not in pandas.
Common questions
- When should I use SUMPRODUCT instead of SUMIFS?
- Use SUMPRODUCT when you need OR logic, calculated conditions such as MONTH(date)=3, or a comparison between two columns. For plain AND conditions SUMIFS is shorter and faster.
- How do I write OR inside SUMPRODUCT?
- Add the tests instead of multiplying them: =SUMPRODUCT((($B$2:$B$500="West")+($B$2:$B$500="East"))*$D$2:$D$500). A row that matches either region counts once.
- Why does SUMPRODUCT return 0?
- Usually no row matched. Check for extra spaces in the text or numbers stored as text, which make "West " differ from "West".
Got a version of this with your own ranges?
Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.