Arguments
sum_range- The cells to add. Note this comes first, unlike SUMIF.criteria_range1- The first range to test.criteria1- The first condition.
SUMIFS
Adds cells that meet multiple conditions across several ranges.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)=SUMIFS(C2:C50, A2:A50, "West", B2:B50, ">100")Returns: Sum of C where A is West AND B > 100
Every criteria pair must be true for a row to be included.
How SUMIFS evaluates
SUMIFS evaluates every criteria pair against the same row and adds the sum_range cell only when all of them pass. The conditions are joined with AND, always - there is no argument that switches to OR, and no way to nest an alternative inside a single call. Each pair is a range and a criterion, and the pairs are read left to right until the arguments run out.
The argument order is the reverse of SUMIF and there is no way to shorten it: sum_range comes first and is mandatory, then at least one criteria_range and criterion. That inversion exists because the pairs are variable in number, so the fixed argument has to lead. It is also the single most common mistake when converting a SUMIF to a SUMIFS - the ranges are correct but shifted by one position, and the result is a plausible wrong total rather than an error.
Every range in the call must be the same height and width, including sum_range. Unlike SUMIF, Excel does not resize anything here; a mismatch is refused outright with #VALUE!. That strictness is a feature, because a SUMIFS with misaligned ranges would otherwise be comparing values from different records.
- All conditions must hold on the same row
- The test is per position, not per range. A row where criteria_range1 matches and criteria_range2 does not contributes nothing, even if some other row satisfies the second condition. Two criteria against the same range - "West" and "East" - can therefore never both be true, and the result is 0.
- Ranges must be identical in shape
- Every criteria_range and sum_range must have the same number of rows and columns. A2:A100 paired with B2:B99 gives #VALUE! rather than aligning on what they have in common. Whole-column references are consistent by definition, which is one reason they are common in SUMIFS.
- Criteria are strings with the same parser as SUMIF
- Operators are recognised at the front of the string: ">100", "<>Closed", ">="&TODAY(). Text comparison is case-insensitive, wildcards * and ? work on text criteria, and ~ escapes a literal wildcard character.
- Only numeric cells in sum_range are added
- Text and booleans in the sum range are skipped rather than coerced. A blank sum cell on a matching row contributes zero without excluding the row from the match count.
- Date criteria compare as numbers
- A date criterion must be built as ">="&DATE(2026,4,1) or ">="&$A$1. A date typed inside the quotes is compared as text against numeric serials and matches nothing, which is why date-bounded SUMIFS silently return 0.
- Errors propagate from any range
- An error value anywhere in sum_range or in any criteria_range returns that error for the whole formula, regardless of whether that row would have matched.
Errors SUMIFS returns
| Error | What it means and how to fix it |
|---|---|
| #VALUE! | The ranges are not all the same shape, or a criteria_range is missing its criterion so the argument count is even. It also appears when any range lives in a closed workbook, which SUMIFS cannot read. |
| #NAME? | A criterion was written unquoted (>100 instead of ">100"), or a named range in one of the arguments no longer exists. |
| #N/A or #DIV/0! showing through | Propagated from a cell inside one of the ranges rather than produced by SUMIFS. Locate the cell and fix or clear it - wrapping the SUMIFS in IFERROR hides a data problem rather than solving it. |
| #REF! | One of the ranges refers to rows or columns that have been deleted. Rebuild the reference; a SUMIFS with many arguments makes it easy to miss which one broke. |
| 0 with no error | Every row failed at least one condition. Comment out the pairs one at a time - or run the equivalent COUNTIFS - to find which criterion is excluding everything. Date criteria written as text are the usual culprit. |
More examples
=SUMIFS($D$2:$D$5000, $A$2:$A$5000, ">="&$G$1, $A$2:$A$5000, "<"&EDATE($G$1,1), $B$2:$B$5000, $G$2)Returns: One month of amounts for one region
The same date column appears twice to bound a period from both ends - the standard way to write a month-to-date total that follows a date in G1.
=SUMIFS($C$2:$C$400, $B$2:$B$400, "<>", $A$2:$A$400, "<>Cancelled")Returns: Total excluding blanks and cancelled rows
Two negative criteria: "<>" on its own excludes empty cells, and "<>Cancelled" excludes one status. Negations are how SUMIFS expresses "everything except".
=SUMIFS($E$2:$E$900, $C$2:$C$900, "*"&$H$2&"*", $D$2:$D$900, ">0")Returns: Total for rows whose description contains a keyword and has a positive quantity
Wrapping a cell in asterisks builds a contains-anywhere criterion, and the numeric pair filters out zero and negative quantities in the same pass.
Compatibility
| Application | Support |
|---|---|
| Excel 2007-2016 | Supported from Excel 2007 onward. A workbook saved as .xls for Excel 2003 loses the function entirely. |
| Excel 2019 | Identical behaviour, up to 127 criteria pairs in one call. |
| Excel 2021 / Microsoft 365 | Unchanged. An array of criteria now spills one total per criterion rather than needing an array entry. |
| Google Sheets | Same syntax and same argument order. Sheets accepts regular-expression criteria when regex matching is enabled for the file. |
| LibreOffice Calc | Supported with the same arguments; wildcard versus regular-expression handling follows the setting under Tools, Options, Calc, Calculate. |
Does SUMIFS Work in Google Sheets?
Yes. SUMIFS uses the same syntax in Google Sheets and LibreOffice Calc, so you can paste these formulas straight across.
In the ExcelTool editor
The ExcelTool editor implements SUMIFS with sum_range first followed by any number of criteria pairs, and returns #VALUE! when the argument count is even, which means a pair is incomplete.
All criteria ranges and the sum range must have the same number of rows and columns or the result is #VALUE! - the same strictness as Excel. Criteria use the shared parser behind SUMIF, COUNTIF and AVERAGEIF, so operators, case-insensitive text comparison, wildcards and tilde escaping all behave identically.
Only numeric cells in the sum range are added; text and booleans there are ignored. An error value found in the sum range on a matching row is returned as the result of the whole formula.
You can try any of these formulas in the free in-browser spreadsheet editor - it opens .xlsx files, recalculates as you type, and needs no signup.
SUMIFS questions
Because SUMIFS accepts a variable number of criteria pairs, so the one fixed argument has to come before them - Excel cannot know where the pairs end. SUMIF predates it and has a fixed three-argument shape, so the optional sum_range could sit at the end. Converting a working SUMIF is therefore not a matter of adding arguments: the existing ones have to be reordered, and forgetting to do so gives a wrong total rather than an error.
SUMIFS joins its conditions with AND, so two criteria on one column can never both be true. Add two SUMIFS calls together, or pass an array of criteria and wrap the result in SUM: =SUM(SUMIFS(C2:C50, A2:A50, {"West","East"})). The array form returns one total per criterion and SUM adds them - just make sure the two criteria cannot match the same row, or that row is counted twice.
A date typed inside the criterion quotes is compared as text against date serial numbers and never matches. Build the criterion by concatenation instead: ">="&DATE(2026,1,1), or ">="&$G$1 pointing at a real date cell. The other possibility is that the column holds text that looks like dates rather than actual dates - a quick =ISNUMBER(A2) on one of them settles it.
Excel allows up to 127 range/criteria pairs in a single SUMIFS. Reaching anywhere near that is a sign the logic belongs somewhere else - a helper column that reduces several conditions to one flag, or a pivot table. Long SUMIFS calls are also slow, because every additional pair is another full pass over the rows.