Arguments
range- The range checked against the criteria.criteria- The condition, e.g. ">100", "West", or a cell reference.sum_range(optional) - The cells to add. If omitted, range itself is summed.
SUMIF
Adds the cells in a range that meet a single condition.
Syntax
=SUMIF(range, criteria, [sum_range])=SUMIF(A2:A50, "West", B2:B50)Returns: Total of B where A is "West"
Sums the sum_range on rows where the criteria range matches.
=SUMIF(B2:B50, ">100")Returns: Sum of values over 100
With no sum_range, the tested range is summed.
How SUMIF evaluates
SUMIF walks a range cell by cell, tests each one against a single criterion, and adds the corresponding cell of sum_range every time the test passes. The correspondence is positional: the third cell of range pairs with the third cell of sum_range, regardless of what the two ranges are called or where they sit. When sum_range is omitted, the tested range is also the summed range, which is what makes =SUMIF(B2:B50, ">100") read so cleanly.
Criteria are the part that surprises people, because a criterion is a string, not an expression. ">100" is text that SUMIF parses into an operator and an operand. That is why a comparison against a cell has to be built by concatenation - ">"&A1 - rather than written as >A1, and why a criterion with no operator is treated as an equality test. The parser also accepts * and ? as wildcards on text criteria, with ~ as the escape character.
One condition is the limit. For two or more, SUMIFS takes over, with the sum range moved to the front. SUMIF survives mainly because it is shorter to type and because millions of existing workbooks use it; new formulas are usually clearer written as SUMIFS even with a single pair.
- Criteria are compared loosely
- Text comparison ignores case, so "West" and "WEST" are the same criterion. A numeric criterion also matches a number stored as text and vice versa, which is the opposite of how VLOOKUP behaves and a frequent source of confusion between the two.
- Only numbers are added
- Text and boolean values in sum_range are skipped rather than coerced, so a column mixing 100 and the text "100" totals only the real numbers. Blank cells contribute nothing and are not counted as zero.
- Wildcards apply to text only
- "*North*" matches any cell containing North; "N?rth" matches one character in the gap. A wildcard in a criterion aimed at numbers has no effect, because the value is never converted to text before testing.
- Comparison against a cell needs concatenation
- ">"&A1 builds the criterion string at calculation time. Writing ">A1" tests for the literal text "A1" being greater, which matches nothing. The same applies to dates: ">="&DATE(2026,1,1) rather than a date typed inside the quotes.
- sum_range is resized to match range
- Excel takes only sum_range's top-left cell seriously and extends it to the shape of range. =SUMIF(A2:A50, "West", B2) quietly sums B2:B50. It works, but it hides the intent and breaks the moment the ranges are meant to differ in size.
- Errors in the range propagate
- A single #N/A anywhere in range or sum_range makes the whole SUMIF return that error. Clean the source column, or aggregate with SUMPRODUCT over an IFERROR if the errors are legitimate and must be ignored.
Errors SUMIF returns
| Error | What it means and how to fix it |
|---|---|
| #VALUE! | Usually a cross-workbook reference: SUMIF cannot read a range in a workbook that is closed. Open the source file, or copy the data in. It also appears when range is not a range at all but a single computed value. |
| #N/A, #DIV/0!, #REF! and friends | Not produced by SUMIF itself - they are propagating from a cell inside range or sum_range. Find the offending cell with =COUNTIF(range, "#N/A") or by filtering for errors, and fix it at source. |
| #NAME? | The criterion was written without quotes, as in =SUMIF(A:A, >100, B:B). Excel reads the bare comparison as a name it does not know. Quote the whole criterion. |
| 0 with no error | The commonest failure. Either nothing matched - check for trailing spaces and for numbers stored as text in the criteria column - or sum_range holds text that looks numeric. Test the criterion alone with COUNTIF to see whether the problem is matching or summing. |
| #SPILL! | Only in Excel 2021 and 365, when the criteria argument is itself an array of criteria: the result is one total per criterion and needs empty cells to spill into. |
More examples
=SUMIF($A$2:$A$500, ">="&DATE(2026,1,1), $C$2:$C$500)Returns: Total of C for dates on or after 1 January 2026
Dates are numbers, so a date criterion is built by concatenating the operator onto DATE rather than typing the date inside the quotes, where it would be compared as text.
=SUMIF($B$2:$B$200, "<>", $B$2:$B$200)Returns: Total of every non-empty cell in the column
The bare "<>" criterion means "not blank". Useful when a column mixes numbers with blanks that a plain SUM would treat identically but a count would not.
=SUMIF($A$2:$A$500, E2&"*", $C$2:$C$500)Returns: Total for every code starting with the prefix in E2
Concatenating a cell with an asterisk builds a prefix wildcard at calculation time, so one column of prefixes in E drives a whole block of subtotals.
Compatibility
| Application | Support |
|---|---|
| Excel 2007-2016 | Fully supported. Criteria strings, wildcards and the resized sum_range all behave as described. |
| Excel 2019 | Identical behaviour; no changes to the function in this release. |
| Excel 2021 / Microsoft 365 | Unchanged, but an array of criteria now spills one total per criterion instead of needing Ctrl+Shift+Enter. |
| Google Sheets | Same syntax. Sheets can also take a regular expression criterion if regex matching is enabled for the file, which Excel cannot. |
| LibreOffice Calc | Supported. Whether * and ? behave as wildcards depends on Tools, Options, Calc, Calculate - files converted from Excel default to wildcards. |
Does SUMIF Work in Google Sheets?
Yes. SUMIF 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 SUMIF with two or three arguments and sums the tested range itself when sum_range is omitted, as Excel does.
Criteria are parsed the same way as in Excel: the operators <, <=, >, >=, <> and = are recognised at the start of the string, an operand that reads as a number is compared numerically, TRUE and FALSE become booleans, and text comparison is case-insensitive. Wildcards * and ? are supported on equality and <> criteria, with ~ escaping a literal one, and a criterion supplied as an empty cell matches blank cells.
Only numeric cells in the summed range contribute; text and booleans there are skipped, and any error value inside the summed range is returned as the result of the whole formula.
One deliberate difference from Excel: the editor does not resize sum_range from its top-left cell. If range and sum_range are different shapes it returns #VALUE! rather than silently extending the shorter one.
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.
Build a SUMIF formula
Fill in your ranges and copy a ready-to-paste formula - no signup.
SUMIF questions
Test the criterion on its own with COUNTIF over the same range and criteria. If COUNTIF also returns 0 the problem is matching - usually trailing spaces in the criteria column or a criterion cell that holds text where the range holds numbers. If COUNTIF finds rows but SUMIF returns 0, the problem is the sum range: its values are text that looks numeric, and SUMIF skips text rather than coercing it.
Concatenate the operator to the reference: ">"&E1, not ">E1". The criterion argument is a string that Excel parses, so anything inside the quotes is taken literally - ">E1" looks for values greater than the two-character text "E1", which matches nothing numeric. The same pattern covers dates (">="&E1), text with wildcards (E1&"*") and negation ("<>"&E1).
In Excel it does not have to be, but it should be. Excel reads only the top-left cell of sum_range and then extends it to the same shape as range, so =SUMIF(A2:A50, "West", B10) actually sums B10:B58 - which is almost never what was meant. Always write both ranges out in full and anchor them with $ so copying the formula cannot pull them out of alignment.
Yes, with wildcards: =SUMIF(A2:A100, "*refund*", B2:B100) matches any cell whose text contains "refund", in any position and any case. Use ? for a single unknown character. If you need to match a literal asterisk or question mark, prefix it with a tilde: "~*" finds cells containing an actual asterisk.