SUMIF Function in Excel, Google Sheets, Airtable & Notion
SUMIF adds the numbers in one range for the rows where another range meets a condition, for example the total of all sales in the East region.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | SUMIF(range, criteria, [sum_range]) |
| Google Sheets | ✔ Built in | SUMIF(range, criterion, [sum_range]) |
| Airtable | ✘ Not available | — Airtable formulas only see one record. Link the records to a summary record and add a rollup field with SUM(values), then turn on “Only include linked records that meet certain conditions” to set the criteria. For a view, the summary bar plus a filter gives the same number. |
| Notion | ≈ Workaround | prop("Orders").filter(current.prop("Region") == "East").map(current.prop("Amount")).sum()Filter a relation (or list), pick the number with map, then sum(). == is case-sensitive, unlike SUMIF. |
| Excel | =SUMIF(A2:A7, "East", B2:B7) | open excel.new ↗ |
|---|---|---|
| Google Sheets | =SUMIF(A2:A7, "East", B2:B7) | open sheets.new ↗ |
| Airtable | ✘ No equivalent. Airtable formulas only see one record. Link the records to a summary record and add a rollup field with SUM(values), then turn on “Only include linked records that meet certain conditions” to set the criteria. For a view, the summary bar plus a filter gives the same number. | |
| Notion workaround | prop("Orders").filter(lower(current.prop("Region")) == "east").map(current.prop("Amount")).sum() | |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
Region Amount East 120 West 85 East 240 North 60 East 95 West 150
The Notion formula assumes a relation property Orders to a database with Region, Product and Amount properties. Text is compared in lower case to copy Excel's case-insensitive matching; wildcards become a test() regular expression.
Arguments
| Argument | What it means |
|---|---|
range | The cells to test against the criteria, e.g. a Region column. |
criteria | The condition, in quotes: "East", ">100", "<>Paid", "A*". A number or a cell reference also works. To use a cell in a comparison, join it: ">"&E1. |
[sum_range] optional | The cells to add. If omitted, the cells in range are added. Should be the same size as range. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=SUMIF(A2:A7, "East", B2:B7) | 455 | Tester data: 120 + 240 + 95. | |
=SUMIF(B2:B7, ">100") | 510 | No sum_range: adds the amounts that are themselves over 100. | |
=SUMIF(A2:A7, "<>East", B2:B7) | 295 | Everything except East. | |
=SUMIF(A2:A7, "*st", B2:B7) | 690 | Wildcard: East and West both end in “st”. |
Examples use Excel / Google Sheets syntax unless noted.
How SUMIF works
SUMIF is the everyday way to total by category: sales per region, hours per project, spending per category. The range is the column you test, the criteria is the condition, and the sum_range is the column you add up. Leave sum_range out when the condition is about the numbers themselves, as in =SUMIF(B2:B100, ">100"). Text matching ignores case, and * (any characters) and ? (one character) work as wildcards; put ~ before them to match a real asterisk or question mark.
Criteria are written as text with the operator inside the quotes: ">=100", "<>Closed". To compare with a cell, join the operator to it: ">"&E1, not ">E1", which would look for the literal text “E1”. "" matches blank cells and "<>" matches non-blank ones. For dates, use ">="&DATE(2025,1,1). If you need more than one condition, use SUMIFS, which also puts the sum range first.
The range arguments must be real cell ranges: Excel rejects an array constant such as {1,2,3} there. Numbers stored as text in the sum range are skipped, like in SUM. Airtable has no SUMIF; the equivalent is a rollup field with SUM(values) and a condition on which linked records to include. In Notion, filter a relation and sum: prop("Orders").filter(current.prop("Region") == "East").map(current.prop("Amount")).sum().
Frequently Asked Questions
- How do I use a cell value in SUMIF criteria?
- For equality just use the cell:
=SUMIF(A:A, E1, B:B). For comparisons join the operator:=SUMIF(B:B, ">"&E1). - Why does SUMIF return 0?
- Usually the criteria does not match because of extra spaces, or the amounts are stored as text. Check a single row with
=A2="East"and=ISNUMBER(B2). - Is SUMIF case-sensitive?
- No. "east" and "EAST" match the same rows. For a case-sensitive sum use
=SUMPRODUCT(--EXACT(A2:A7, "East"), B2:B7).