In Excel: use SUMPRODUCT with a Boolean filter to sum or count conditionally without array entry — =SUMPRODUCT((A:A=E2)*(C:C)) sums column C only for rows where column A equals E2.
On this page8
Formula: SUMPRODUCT with Criteria
Identify the sum column (C:C) and the criteria column(s) with their match value(s).
Build the Boolean filter: (A:A=E2) — this evaluates to TRUE/FALSE for each row.
Multiply the filter by the data column: (A:A=E2)*(C:C)
Wrap in SUMPRODUCT: =SUMPRODUCT((A:A=E2)*(C:C))
Add more criteria by multiplying more Boolean arrays: =SUMPRODUCT((A:A=E2)*(B:B=F2)*(C:C))
For a count instead of a sum: =SUMPRODUCT((A:A=E2)*1)
What this does
SUMPRODUCT is a flexible array function that multiplies corresponding elements of two or more arrays and sums the results. When one array is a Boolean filter — (A:A=E2) produces 1 for rows where A matches and 0 elsewhere — multiplying it by a data array (C:C) effectively zeros out non-matching rows before summing. This gives SUMPRODUCT conditional-sum power without needing Ctrl+Shift+Enter. A double-unary (--) coerces TRUE/FALSE to 1/0 explicitly in some formulas, though multiplication already does this implicitly. SUMPRODUCT also enables counting (replace the sum array with 1: =SUMPRODUCT((A:A=E2)*1)) and multi-criteria filtering. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. Treat “excel sumproduct multiple criteria” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected cells shows up while it is still harmless. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. That turns a text operation that turns messy entries into clean, usable data into a method you can reuse, explain, and defend when the workbook leaves your screen.
A worked example
Scenario: column A contains product categories, column B contains regions, column C contains sales amounts. Sum sales where category equals E2: =SUMPRODUCT((A:A=E2)*(C:C)). Add a second filter: =SUMPRODUCT((A:A=E2)*(B:B=F2)*(C:C)) sums only rows where BOTH columns match — equivalent to SUMIFS(C:C,A:A,E2,B:B,F2) but also works in Excel 2003 and Google Sheets where SUMIFS is unavailable. For a count: =SUMPRODUCT((A:A=E2)*1) counts rows matching E2. Master SUMPRODUCT when you need conditional aggregation that goes beyond what SUMIFS supports — OR logic across criteria, combining counts and sums in one formula, or supporting workbooks that must work in Excel 2003 or Google Sheets. It is the Swiss-army-knife formula for data analysis: slower than SUMIFS on huge datasets but far more expressive, and it never requires Ctrl+Shift+Enter. If there is any chance you will reuse this, drop it into a small template tab right now: a labelled input area on the left and the formula beside it, checked once against the tool above. Next time the same question comes up, the answer is a single paste away instead of a rebuild from memory.
In Google Sheets
If you are in Google Sheets rather than Excel, the good news is that the formula shown here is identical and the workflow barely changes — menus sit across the top instead of in a ribbon, and a few function names differ slightly, but anything you build here moves across with little or no rework. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. The short version of “excel sumproduct multiple criteria”: the answer is at the top of this page, the tool proves it on your own numbers, and the sections above explain why it holds so the next variation does not stump you. Excel rewards people who reference cells instead of typing values and who keep inputs separate from formulas, because that is what makes a result you can audit months later. Build it once, deliberately, with the live tool as a check, and you convert a one-off lookup into a reusable skill — which is the whole point of learning the why and not just the what.
Common mistakes
- Using full column references on large datasets —
SUMPRODUCTevaluates every row including blanks. For better performance, restrict to the actual data range:=SUMPRODUCT((A2:A1000=E2)*(C2:C1000)). - Forgetting to multiply the final data array —
=SUMPRODUCT((A:A=E2))without *(C:C) counts matching rows instead of summing their values. - Confusing
SUMPRODUCTwithSUMIFSfor simple single-criterion sums —SUMIFSis faster and more readable for straightforward conditional sums. UseSUMPRODUCTwhen you needORlogic, complex Boolean expressions, or need to support older Excel versions withoutSUMIFS.
Frequently asked questions
What can SUMPRODUCT do that SUMIFS cannot?
SUMPRODUCT supports OR logic naturally: =SUMPRODUCT(((A:A="North")+(A:A="South")>0)*(C:C)) sums rows where the region is either North or South. SUMIFS requires separate formulas added together for OR logic. SUMPRODUCT also handles more complex Boolean expressions and nested conditions in a single formula.
Is SUMPRODUCT slower than SUMIFS?
Yes — SUMPRODUCT evaluates every row in the referenced range, while SUMIFS uses internal optimizations. For simple conditional sums, prefer SUMIFS. Use SUMPRODUCT when you need its OR-array capability or when building complex multi-condition formulas that SUMIFS cannot express.
What does the -- (double negative) do in SUMPRODUCT?
-- is a double unary negation that converts TRUE/FALSE to 1/0 explicitly. It is sometimes needed when working with text comparisons that do not coerce automatically. Multiplication (* ) already converts TRUE/FALSE to 1/0 implicitly, so --( ) is often unnecessary when multiplying arrays together.