SUMIFS Function in Excel, Google Sheets, Airtable & Notion
SUMIFS adds the numbers in a range for rows that meet all of the conditions you give it, for example East-region sales of Widgets.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) |
| Google Sheets | ✔ Built in | SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]) |
| 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. Add several conditions; they are combined with AND by default. |
| Notion | ≈ Workaround | prop("Orders").filter(current.prop("Region") == "East" and current.prop("Product") == "Widget").map(current.prop("Amount")).sum()Combine conditions inside one filter with and (or or). |
| Excel | =SUMIFS(C2:C7, A2:A7, "East", B2:B7, "Widget") | open excel.new ↗ |
|---|---|---|
| Google Sheets | =SUMIFS(C2:C7, A2:A7, "East", B2:B7, "Widget") | 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. Add several conditions; they are combined with AND by default. | |
| Notion workaround | prop("Orders").filter(lower(current.prop("Region")) == "east" and lower(current.prop("Product")) == "widget").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 Product Amount East Widget 120 West Gadget 85 East Gadget 240 North Widget 60 East Widget 95 West Widget 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 |
|---|---|
sum_range | The cells to add. Note it comes first, unlike SUMIF. |
criteria_range1 | The first range to test. Must be the same size as sum_range. |
criteria1 | 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. |
[criteria_range2, criteria2], ... optional | More range/condition pairs, up to 127. A row is added only if it meets every condition. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=SUMIFS(C2:C7, A2:A7, "East", B2:B7, "Widget") | 215 | Tester data: East Widgets are 120 and 95. | |
=SUMIFS(C2:C7, A2:A7, "East", C2:C7, ">=100") | 360 | The sum range can also be a criteria range: East orders of 100 or more. | |
=SUMIFS(C2:C7, B2:B7, "Widget", C2:C7, "<100") | 155 | Small Widget orders: 60 + 95. | |
=SUMIFS(C:C, D:D, ">="&DATE(2025,1,1), D:D, "<"&DATE(2025,2,1)) | January 2025 total | Two conditions on one date column make a date range. |
Examples use Excel / Google Sheets syntax unless noted.
How SUMIFS works
SUMIFS is SUMIF for several conditions, and many people use it even for one condition because the argument order is easier to remember: what to add first, then pairs of where to look and what to look for. All conditions must be true for a row to be added (AND logic). The criteria work exactly like in SUMIF: operators inside quotes, * and ? wildcards, case-insensitive text, and ">"&E1 to compare with a cell.
Every criteria range must be the same size as the sum range, or SUMIFS returns #VALUE! (SUMIF silently resizes instead). The classic use beyond categories is a date window: =SUMIFS(C:C, D:D, ">="&G1, D:D, "<"&G2) totals everything from the date in G1 up to, but not including, G2. For OR logic (East or West) add two SUMIFS together, or use an array constant: =SUM(SUMIFS(C2:C7, A2:A7, {"East","West"})).
Airtable handles this with a rollup field whose “only include linked records that meet certain conditions” option can hold several conditions. In Notion, put all the conditions inside one filter() joined with and, then map to the amount and sum().
Frequently Asked Questions
- How do I sum between two dates?
- Use the date column twice:
=SUMIFS(C:C, D:D, ">="&G1, D:D, "<="&G2). - Can SUMIFS use OR instead of AND?
- Not directly. Add separate SUMIFS together, or wrap SUMIFS with an array of criteria in SUM:
=SUM(SUMIFS(C:C, A:A, {"East","West"})). - Why does SUMIFS return #VALUE!?
- The ranges are different sizes, for example C2:C100 and A2:A99.