SUMIF in Excel: Syntax, Examples and Errors
Updated 2026-10-13 · Free · Works in Excel & Google Sheets
Syntax
=SUMIF(range, criteria, [sum_range])Quick answer
SUMIF adds the cells that meet one condition: =SUMIF($C$2:$C$500, "New York", $D$2:$D$500) totals column D for every row where column C holds New York. Criteria accept numbers, text, dates and wildcards, and matching is not case-sensitive. A result of 0 usually means numbers stored as text or missing quotes around the criteria.
Syntax
=SUMIF(range, criteria, [sum_range])| Argument | What it means |
|---|---|
| range | The cells to evaluate: a single column or row, e.g. $C$2:$C$500. |
| criteria | The condition a cell must meet: a number, text in double quotes, a comparison like ">100", or a cell reference such as G1. |
| sum_range | Optional. The cells to add when the condition is met. Omit it and SUMIF adds the range itself. |
Depending on your regional settings, arguments are separated by semicolons instead of commas: =SUMIF($C$2:$C$500; "New York"; $D$2:$D$500). This is standard in countries where the comma is the decimal separator.
Basic examples
=SUMIF($C$2:$C$500, "New York", $D$2:$D$500)Adds the sales in column D for every row where the city in column C is exactly "New York". Text criteria always need double quotes, and matching is case-insensitive: "new york" sums the same.
=SUMIF($D$2:$D$500, ">100")With no third argument, SUMIF adds the range against itself: every value above 100. Comparison operators (>, >=, <, <>) always go inside the quoted string, glued to the number.
=SUMIF($B$2:$B$500, ">2026-01-01", $D$2:$D$500)Adds the sales after January 1, 2026. The comparison stays inside the quotes; the cells must hold real dates, not text that looks like one.
=SUMIF($A$2:$A$500, "*@gmail.com", $D$2:$D$500)Adds the revenue of the emails ending in @gmail.com. The * wildcard matches any number of characters, ? matches exactly one, and ~ escapes a literal * or ? in the text.
sum_range is stretched from its top-left cell to match range: =SUMIF($C$2:$C$500, "New York", $D$2) still sums $D$2:$D$500. Handy, but keep both ranges the same size anyway so the formula stays readable and audit-proof.
Common errors and how to fix them
| Symptom | Most common cause | Fix |
|---|---|---|
| #NAME? | SUMIF is misspelled — or your Excel is set to another language, where the function has a localized name (SUMAR.SI, SOMME.SI, SUMMEWENN, SOMA.SE). | Type =SUM( and let autocomplete complete the name, or open the file in the matching language version of Excel. |
| The result is always 0 | Numbers stored as text, invisible trailing spaces, criteria written without its quotes, or the wrong column passed as sum_range. | Convert text-numbers, clean the spaces, keep the criteria in double quotes, and check the sum column letter. |
| The total is clearly too big | range and sum_range drifted out of alignment after inserting rows: sum_range grows from its top-left cell, silently including the header or a summary row. | Rewrite both ranges with the same height, locked with $ signs, and never let the top of sum_range start above the first data row. |
| Dates are never summed | The "dates" are text imported from another system, so ">2026-01-01" never matches. | Convert the column to real dates first — a date criteria only works on true date values. |
Trailing spaces are the quiet killer here too: the total comes back short with no error message. Clean the whole column in one click before summing anything.
SUMIF vs. SUMIFS
| SUMIF | SUMIFS | |
|---|---|---|
| Conditions per formula | One | Two to 127 |
| Combines conditions with AND | No | Yes |
| Position of the sum range | Third argument, optional | First argument, required |
| Available in | Every Excel version (and Google Sheets) | Excel 2007+ (and Google Sheets) |
=SUMIFS($D$2:$D$500, $C$2:$C$500, "New York", $D$2:$D$500, ">100")Move to SUMIFS the moment you need two conditions at once — sales above 100 in New York, for example. Mind the flipped argument order: in SUMIFS the sum range comes first and is mandatory. Plain SUM totals everything; SUMIF and SUMIFS cover everything conditional.
SUMIF in Google Sheets
Google Sheets supports SUMIF with the same arguments as Excel, so the formulas above can be pasted over unchanged.
- Comparison operators, wildcards and case-insensitive matching behave identically in Sheets.
- In regional settings that use a comma as the decimal separator (much of Europe and Latin America), type semicolons between arguments: =SUMIF($C$2:$C$500; "New York"; $D$2:$D$500). Excel behaves the same way.
- Google Sheets also offers SUMIFS with the same flipped argument order — multi-condition totals work the same in both apps.
- Sheets adds the QUERY function, which can filter and total in one formula; in Excel the equivalents are SUMIFS or a pivot table.
Limitations and pro tips
- SUMIF cannot compare two ranges row by row (add the sales where D is greater than E) — that needs SUMPRODUCT: =SUMPRODUCT(($D$2:$D$500>$E$2:$E$500)*$D$2:$D$500).
- One condition only. For OR logic, add two SUMIFs: =SUMIF(range, ">100")+SUMIF(range, "<0").
- It cannot sum by cell color — filter by color and read the status bar, or add a helper column.
- Lock ranges with $ signs ($C$2:$C$500) so the range does not slide sideways when you copy the formula down.
- Cell references glue into criteria with &: =SUMIF($C$2:$C$500, G1, $D$2:$D$500) or ">"&G1 for comparisons — no need to edit the formula when the threshold changes.
Before totaling, you often need to see the matching rows first: filter them, sanity-check the list, then let SUMIF do the math. The free tools below help with both.
Related Tools
Related Functions
Frequently Asked Questions
What does SUMIF do in Excel?
It adds the cells that meet a single condition — sales above a threshold, revenue of one city, totals after a deadline or wildcard patterns: =SUMIF($C$2:$C$500, "New York", $D$2:$D$500). Matching is case-insensitive, and one formula handles exactly one condition.
How do I sum cells greater than a number?
Put the operator inside quoted criteria and drop the third argument: =SUMIF($D$2:$D$500, ">100"). The quotes are required — without them Excel reads >100 as an invalid formula. Use >= for or-equal, and & to glue in a cell reference: ">"&D1.
Why is my SUMIF returning 0?
Almost always a data problem, not a formula problem: numbers stored as text, invisible trailing spaces, criteria missing its quotes, or the wrong sum column. Convert text-numbers with VALUE(), trim the spaces, and check that "New York" really is wrapped in double quotes.
Can SUMIF criteria reference a cell?
Yes: =SUMIF($C$2:$C$500, G1, $D$2:$D$500) uses the value in G1 as the criteria. For comparisons, glue the operator to the reference: ">"&G1. The formula then follows the cell, so you can change the threshold without editing the formula.
Is SUMIF case-sensitive?
No — SUMIF treats "NYC", "nyc" and "Nyc" as the same value. If you truly need a case-sensitive total, use SUMPRODUCT with EXACT: =SUMPRODUCT(--EXACT(range, "NYC")*sum_range).
When should I use SUMIFS instead of SUMIF?
The moment you have two or more conditions that must all be true: =SUMIFS($D$2:$D$500, $C$2:$C$500, "New York", $D$2:$D$500, ">100"). SUMIF handles one condition; SUMIFS handles 2 to 127 and works in Excel 2007+ and Google Sheets. Note the flipped order: the sum range comes first in SUMIFS.
Does SUMIF work in Google Sheets?
Yes — same name, same arguments, same wildcards and case-insensitive matching. Only the argument separator changes in comma-decimal regional settings, where you type semicolons: =SUMIF($C$2:$C$500; "New York"; $D$2:$D$500).
Ready to try it on your own file?
Open Analyze Excel Data — It's Free