SheetSense

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.

fx
=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

  1. 01($B$2:$B$500="West") returns TRUE for each West row and FALSE for the rest.
  2. 02($C$2:$C$500="Widget") does the same for the product column.
  3. 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.
  4. 04SUMPRODUCT adds up the results into a single number.

The same logic in four tools

Excel
=SUMPRODUCT(($B$2:$B$500="West")*($C$2:$C$500="Widget")*$D$2:$D$500)
Google Sheets
=SUMPRODUCT(($B$2:$B$500="West")*($C$2:$C$500="Widget")*$D$2:$D$500)
SQL
SELECT COALESCE(SUM(revenue), 0) AS total_revenue
FROM sales
WHERE region = 'West'
  AND product = 'Widget';
Python (pandas)
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.