In Excel: use =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2) — it adds the numbers in sum_range only for rows that satisfy every condition you list.
On this page8
Syntax
Arguments
| Argument | required / optional | Description |
|---|---|---|
sum_range | required | The cells to add — and unlike SUMIF this comes FIRST, not last. |
criteria_range1 | required | The first column tested against criteria1. |
criteria1 | required | The condition for criteria_range1, like "West" or ">100". |
criteria_range2, criteria2 | optional | Optional further range/criterion pairs; all conditions must be true (AND logic). |
Related functions
Type =SUMIFS( and select the column of numbers to add as sum_range first — this is the order difference from SUMIF.
Type a comma, select the first column to test, type a comma, and enter its criterion in quotes.
Type a comma and add the next range-and-criterion pair, for example the month column and "Jan".
Repeat for any further conditions, then close the bracket and press Enter.
Lock the ranges with $ if you will copy the formula across a summary table.
What this does
SUMIFS adds numbers across multiple conditions at once, combining them with AND logic: a row is counted only if it passes all of the criteria_range/criteria pairs. The crucial difference from SUMIF is argument order — in SUMIFS the sum_range comes FIRST, followed by each range-and-criterion pair, whereas SUMIF puts the sum_range last. You can stack up to 127 condition pairs. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “sum if excel multiple criteria”. Start on a copy or a tiny sample, keep the affected cells visible, and compare the result with the outcome you expected before you touch the real workbook. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. The point is a workflow that saves repeating the same clicks every week, but the practical win is that someone else can open the file and understand what happened without asking you.
A worked example
Sales are in C2:C100, regions in A2:A100, months in B2:B100. To total sales that are both West AND January: =SUMIFS(C2:C100, A2:A100, "West", B2:B100, "Jan"). It adds a row only when the region is "West" and the month is "Jan", returning say 12750. Note the sum column C2:C100 comes first, before any criteria. SUMIFS is the workhorse for cross-tabulated totals — sales by region and month, costs by department and quarter — letting one formula replace a filtered pivot for many summary tables. 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 “sum if excel 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
- Putting sum_range last as in
SUMIF— inSUMIFSit must come first, or the formula returns 0 or an error. - Using criteria ranges of different heights from sum_range, which throws a
#VALUE!error. - Expecting
ORlogic —SUMIFSrequires every condition to be true; for "either" totals, add twoSUMIFStogether.
Frequently asked questions
How is SUMIFS different from SUMIF?
SUMIFS handles multiple conditions and puts sum_range first; SUMIF handles one condition and puts sum_range last. For a single criterion either works, but the argument order differs, so do not mix them up.
Does SUMIFS use AND or OR logic?
AND — a row contributes only when it meets all the criteria pairs. To total rows meeting one OR another condition, add two SUMIFS formulas together.
Can SUMIFS use comparison operators and wildcards?
Yes. Each criterion can be a quoted comparison like ">=100" or a wildcard pattern like "App*", exactly as in SUMIF, and you can reference cells with concatenation such as ">"&E1.