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. Check the referenced cells, argument order and result type in your workbook. Start with the worked example before changing an input, then compare the changed result with the calculation you expected. An error value or a result of a different type is a reason to inspect the inputs and arguments.

In Google Sheets

The standard worksheet functions used by this example are listed in Google Sheets. Check the cell references, argument separators and input types in the target spreadsheet, then compare the worked result. This formula-availability check does not establish identical menus, keyboard shortcuts or workbook features, and it is not a product execution test. Keep this page bookmarked for the next time the same question comes up. Better still, repeat the steps once in your own workbook — doing it yourself is what turns a copied answer into something you remember. For “sumif multiple criteria”, keep the input cells and the result visible while you check the formula in Excel. Follow the worked example and change one input at a time. The steps explain the workbook calculation; this page does not run a live calculator for that task.

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