SUMPRODUCT vs SUMIFS: which should you use?

The same total, written both ways

The sheet is a small sales log: date, region, rep, product, units and revenue. Total revenue for North is 11,720 with either function. SUMIFS takes the sum range first, then pairs of "range, criteria". SUMPRODUCT turns each test into TRUE or FALSE per row, and multiplying by the revenue column turns those into 1 and 0, so only matching rows are left in the sum.

More conditions work the same way. North laptops: =SUMIFS(F2:F11,B2:B11,"North",D2:D11,"Laptop") adds two more arguments, and =SUMPRODUCT((B2:B11="North")*(D2:D11="Laptop")*F2:F11) multiplies one more test. Both return 6,500. For AND conditions like these, the SUMIFS version is easier to read and to hand to a colleague.

Dates: a date range in SUMIFS, MONTH in SUMPRODUCT

SUMIFS can only test the cells themselves, so a month is written as a date range: =SUMIFS(F2:F11,B2:B11,"North",A2:A11,">="&DATE(2026,11,1),A2:A11,"<"&DATE(2026,12,1)) gives North's November 2026 revenue, 4,140. You cannot write MONTH(A2:A11) as a criteria range; SUMIFS only accepts real ranges there, and Excel rejects the formula.

SUMPRODUCT can run MONTH on every date, which feels more natural. But =SUMPRODUCT((B2:B11="North")*(MONTH(A2:A11)=11)*F2:F11) returns 5,440, not 4,140. MONTH ignores the year, so Ben's laptop sale from 18 November 2025 (1,300) sneaks in. Add a YEAR test, as below, or stick with the date range, which is exact by design.

OR logic: easy in SUMPRODUCT, awkward in SUMIFS

SUMIFS joins its conditions with AND only. For North or West in the same column, add one SUMIFS per value, or pass both values as an array constant and wrap it in SUM: =SUM(SUMIFS(F2:F11,B2:B11,{"North","West"})) returns 16,700. SUMPRODUCT adds the tests instead of multiplying them: =SUMPRODUCT(((B2:B11="North")+(B2:B11="West"))*F2:F11) gives the same 16,700.

OR across two different columns is where SUMPRODUCT clearly wins. North or Laptop: a North laptop row passes both tests, so plain addition gives it a 2 and counts it twice (24,720). Wrapping the sum in >0 turns every match back into a single TRUE. With SUMIFS you would need North plus Laptop minus North-and-Laptop, three SUMIFS, to get the same 18,220.

Tests SUMIFS cannot do at all

Any condition that needs a function on each row is SUMPRODUCT's job. Weekend sales use WEEKDAY: with return type 2, Saturday is 6 and Sunday is 7, so >5 keeps the weekend. Three rows qualify: Saturday 3 October, Sunday 11 October and Saturday 14 November. The SUMIFS alternative is a helper column with =WEEKDAY(A2,2) that you then test with ">5".

Wildcards go the other way. SUMIFS understands them: =SUMIFS(F2:F11,D2:D11,"*top") returns 13,000 for laptops. SUMPRODUCT compares exactly, so a partial match needs ISNUMBER(SEARCH("top",D2:D11)) instead, which gives the same 13,000.

Neither is case-sensitive by default. =SUMIFS(F2:F11,B2:B11,"north") still returns 11,720, and so does B2:B11="north" inside SUMPRODUCT. Only SUMPRODUCT can be made strict: =SUMPRODUCT(EXACT(B2:B11,"north")*F2:F11) returns 0, because every cell says North with a capital N.

Numbers stored as text give different totals

East's Dock sale on 9 November was imported as the text 1200, not a number. SUMIFS skips text in the sum range, so =SUMIFS(F2:F11,B2:B11,"East") returns 4,760. Multiplying with * coerces text that looks like a number, so the SUMPRODUCT version adds the 1,200 and returns 5,960. Two formulas that look equivalent disagree, and neither warns you.

The comma form behaves like SUMIFS: =SUMPRODUCT(--(B2:B11="East"),F2:F11) also returns 4,760, because SUMPRODUCT treats non-numbers in its arguments as 0. And text that is not a number, like n/a, makes the * version fail with #VALUE!.

Do not pick a formula to work around this; fix the data. =COUNTA(F2:F11)-COUNT(F2:F11) returns 1 here: one non-empty cell in the revenue column is not a number. Convert it (the green triangle menu > Convert to Number), and both functions agree on 5,960.

Side by side, and which to pick

Conditions: SUMIFS tests cell values against fixed criteria, joined with AND. SUMPRODUCT tests anything you can calculate per row (MONTH, WEEKDAY, LEN, units x price) and does OR with +.

Matching: SUMIFS supports wildcards (* and ?) and operators in text, like ">=10". SUMPRODUCT compares exactly unless you add SEARCH, and only SUMPRODUCT can be case-sensitive with EXACT.

Data problems: SUMIFS skips text and handles a header row inside the range. SUMPRODUCT with * coerces text numbers, and a header or n/a cell in a multiplied range returns #VALUE!.

Speed and ranges: SUMIFS is usually faster on large sheets and copes well with whole columns like F:F. SUMPRODUCT calculates every cell you give it, so use exact ranges or a table, never whole columns. SUMIFS also returns #VALUE! when it points at another workbook that is closed; SUMPRODUCT still calculates.

Verdict: start with SUMIFS. It is the clearer formula for the common case of one or more AND conditions on columns. Switch to SUMPRODUCT when you need a calculated condition, OR across columns, case-sensitive matching, or links to closed workbooks. SUMIFS is in Excel 2007 and later; SUMPRODUCT works in every version.