SUMIF Multiple Criteria

What this does

SUMIFS extends SUMIF to support multiple simultaneous criteria. The syntax is =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …) with up to 127 criteria pairs. Unlike SUMIF, SUMIFS places the sum_range first and always requires the sum range and criteria ranges to be the same size. All criteria must match simultaneously (AND logic) — use a helper formula or SUMPRODUCT for OR logic across criteria. SUMIFS is available in Excel 2007 and later and in Google Sheets. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. For “sumif multiple criteria”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected cells change, and only then apply the same setup to the full sheet. 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 is what makes a calculation you can defend to a CFO or an auditor useful in real work: repeatable, auditable, and not dependent on memory or luck.

A worked example

Scenario: column A contains regions, column B contains product categories, column C contains sales amounts. You want to sum sales where region equals the value in E2 AND category equals the value in F2. =SUMIFS(C:C,A:A,E2,B:B,F2) sums every row in C where the same row in A matches E2 and the same row in B matches F2. Change E2 to "North" and F2 to "Electronics" to get total electronics sales in the North region. For date ranges: =SUMIFS(C:C,A:A,">="&E2,A:A,"<="&F2) sums rows where A falls between dates in E2 and F2. SUMIFS is the workhorse for conditional aggregation in Excel — use it any time you need a subtotal filtered by two or more conditions. It is faster than SUMPRODUCT on large datasets, clearer to read than array formulas, and available in every modern Excel version. Master it for finance models, sales dashboards, and any structured data table where you slice totals by multiple dimensions. A practical tip before you scale it up: build it once on a small block of test data, confirm the number against the tool on this page, and only then point it at your real sheet. That one habit catches almost every mistake while it is still cheap to fix, long before a wrong figure reaches a report or a colleague.

In Google Sheets

Everything above works in Google Sheets too. Excel and Sheets share the formula syntax used here; only the surrounding menus are arranged differently. That portability is deliberate — learn it once and it follows you between the two tools and across Windows and Mac. Keep this page bookmarked for the next time the same question comes up. Better still, rebuild the example once in your own sheet — doing it yourself, with the tool above to check against, is what turns a copied formula into a technique you own. If you take one thing from this page on “sumif multiple criteria”, make it the habit rather than the keystrokes: set the problem up with labelled inputs, reference those cells, and let Excel do the recomputing. Bookmark the page for the syntax, but do the example once in a blank sheet and check it against the tool above — five minutes of hands-on practice fixes the method in memory far better than re-reading, and it surfaces the small snags while they are still harmless. After that the technique is genuinely yours: faster than searching for it again, and reliable enough to drop into work that other people depend on.

Common mistakes

• Using SUMIF instead of SUMIFS for multiple criteria — SUMIF only supports one criterion; SUMIFS is the correct function for two or more.
• Mismatching range sizes — all ranges in SUMIFS (sum_range and all criteria_ranges) must cover the same number of rows. A1:A100 and C1:C200 would return #VALUE!.
• Treating SUMIFS as OR logic — SUMIFS requires ALL criteria to match simultaneously. For OR (sum where region=North OR region=South), use =SUMIFS(C:C,A:A,"North")+SUMIFS(C:C,A:A,"South") or SUMPRODUCT.

Frequently asked questions