SUMPRODUCT with multiple criteria: AND, OR and partial text in one formula

How SUMPRODUCT turns criteria into numbers

A comparison on a range, such as A2:A11="North", returns one TRUE or FALSE per row. In arithmetic Excel treats TRUE as 1 and FALSE as 0, so (A2:A11="North")*E2:E11 keeps the revenue of the North rows and turns every other row into 0. SUMPRODUCT then adds the result. Nothing has to be entered with Ctrl+Shift+Enter, because SUMPRODUCT evaluates arrays on its own in every Excel version.

There are two common ways to write it, and both give the same result:

• =SUMPRODUCT((A2:A11="North")*(C2:C11="Mar"),E2:E11): the conditions are multiplied into one 1/0 array, and the sum range is a separate argument.
• =SUMPRODUCT((A2:A11="North")*(C2:C11="Mar")*E2:E11): everything is multiplied in one argument.

What does not work is listing a bare condition as its own argument: =SUMPRODUCT(A2:A11="North",E2:E11) returns 0, because SUMPRODUCT ignores TRUE and FALSE in a separate argument. Multiply the condition by 1, or put two minus signs in front of it (the double unary --(A2:A11="North")), to turn it into numbers first.

Worked example: ten coffee orders, eight SUMPRODUCT formulas

Rows 2 to 11 are the orders of a fictional coffee roaster. Each formula in column E answers one question with multiple criteria. Row 17 shows the double-counting error that row 16 avoids.

Worked example: ten coffee orders, eight SUMPRODUCT formulas

	A	B	C	D	E
1	Region	Product	Month	Bags	Revenue
2	North	Espresso Blend	Jan	40	520.00
3	South	House Decaf	Jan	25	300.00
4	North	House Blend	Feb	60	690.00
5	West	Espresso Blend	Feb	30	405.00
6	South	Espresso Single Origin	Feb	45	630.00
7	North	Espresso Single Origin	Mar	20	300.00
8	West	House Blend	Mar	55	605.00
9	South	House Blend	Mar	35	400.00
10	North	House Decaf	Mar	15	195.00
11	West	Espresso Blend	Mar	50	650.00
12
13	Revenue: North AND March				495.00
14	Count: South AND more than 30 bags				2
15	Revenue: North OR West (one column)				3,365.00
16	Revenue: North OR espresso, with SIGN				3,390.00
17	Same without SIGN (counts 2 rows twice)				4,210.00
18	Bags: espresso AND (Feb OR Mar)				145
19	Count: revenue per bag above 12.50				6
20	Weighted price per bag, North				12.63
21	Check row 13 with SUMIFS				495.00

The orders are fictional. Excel and Google Sheets give the same results.

AND logic: multiply the conditions

A row only counts when every condition is 1, and 1 × 1 is the only product that is not 0. North and March in the example are rows 7 and 10: 300 + 195 = 495. Leave out the sum range and you get a count instead: =SUMPRODUCT((A2:A11="South")*(D2:D11>30)) counts the South orders above 30 bags and returns 2.

For plain AND conditions on columns, SUMIFS gives the same 495 and is easier to read. SUMPRODUCT earns its place when the conditions get harder, as in the next sections.

OR logic: add the conditions, and avoid double counting

Adding two conditions gives OR logic: ((A2:A11="North")+(A2:A11="West")) is 1 for every North or West row. On one column the two tests can never both be true, so the sum is never more than 1 and =SUMPRODUCT(((A2:A11="North")+(A2:A11="West"))*E2:E11) returns 3,365 correctly.

On two different columns a row can meet both conditions. "Region is North or the product is an espresso" is true twice for the North espresso orders, the sum becomes 2 and those rows are counted twice: the naive formula returns 4,210. Wrap the sum in SIGN(), or compare it with >0, to turn every positive value back into 1: =SUMPRODUCT(SIGN((A2:A11="North")+ISNUMBER(SEARCH("Espresso",B2:B11)))*E2:E11) = 3,390. The difference of 820 is exactly the two North espresso orders (520 + 300).

Partial text: ISNUMBER(SEARCH())

Wildcards such as "*Espresso*" do not work inside a SUMPRODUCT comparison, because = compares the whole text. Use SEARCH instead: it returns the position of a word inside each text, or an error if the word is missing, and ISNUMBER turns that into TRUE or FALSE. SEARCH ignores upper and lower case. Use FIND if case matters.

Text tests combine with the other conditions like any other array. The bags of all espresso products sold in February or March are =SUMPRODUCT(ISNUMBER(SEARCH("Espresso",B2:B11))*((C2:C11="Feb")+(C2:C11="Mar")),D2:D11) = 145, an (A OR B) AND C test in one formula.

Criteria on a calculated value

SUMIFS and COUNTIFS can only test a column as it is. SUMPRODUCT can test the result of a calculation row by row. The number of orders that earned more than 12.50 per bag is =SUMPRODUCT(--(E2:E11/D2:D11>12.5)) = 6, without a helper column for the price per bag. The same idea works for a weekday from a date (WEEKDAY(A2:A100)=1), the month of a date or the difference between two columns.

A weighted average with a condition is two SUMPRODUCTs: revenue of the North orders divided by their bags, 1,705 ÷ 135 = 12.63 per bag. A plain average of the four North prices per bag would give each order the same weight, however small it was.

SUMPRODUCT, SUMIFS or FILTER?

Use SUMIFS for simple AND criteria on columns: it is faster on large sheets and easier for the next person to read. Use SUMPRODUCT when you need OR across columns, partial text without wildcards, a calculated criterion or a lookup with several conditions. In Excel 365, SUM(FILTER(E2:E11,(A2:A11="North")*(C2:C11="Mar"))) uses the same 1/0 logic, but it returns an error when nothing matches, while SUMPRODUCT simply returns 0 and works in every version, including Google Sheets.

SUMPRODUCT calculates every row of every range, so keep the ranges to the data (or use an Excel Table) rather than whole columns like A:A, which make a sheet slow.

Common mistakes

• Conditions as separate arguments. =SUMPRODUCT(A2:A11="North",E2:E11) returns 0 because TRUE and FALSE are ignored. Multiply the conditions, or use -- or *1.
• Ranges of different sizes. A2:A11 with E2:E12 returns #VALUE!. Every range must cover the same rows.
• OR across two columns without SIGN. A row that meets both conditions gets a 2 and is counted twice. Wrap the added conditions in SIGN() or test >0.
• Using AND() or OR() inside SUMPRODUCT. These functions return one single TRUE or FALSE for the whole range, not one per row. Use * for AND and + for OR.
• Wildcards in a comparison. B2:B11="*Espresso*" looks for the literal text with asterisks. Use ISNUMBER(SEARCH("Espresso",B2:B11)).
• Whole-column references. SUMPRODUCT((A:A="North")*E:E) calculates a million rows and slows the workbook. Limit the ranges to the data or use a Table.