SUMIFS function
Adds all of its arguments that meet multiple criteria.
Syntax
SUMIFS(sum_range, criteria_range1, criteria1, [criteriaRange2], [criteria2])Square brackets indicate optional arguments.
Arguments
| Argument | Required | Meaning |
|---|---|---|
| sum_range | Yes | The range of cells to sum.A1:A20 |
| criteria_range1 | Yes | The range that is tested using criteria1. criteria_range1 and criteria1 set up a search pair whereby a range is searched for specific criteria. Once items in the range are found, their corresponding values in sum_range are added.B1:B20 |
| criteria1 | Yes | The criteria that defines which cells in criteria_range1 will be added. For example, criteria can be entered as 32, ">32", B4, "apples", or "32".">10" |
| criteriaRange2 | No · repeats | Additional ranges. You can enter up to 127 range pairs.C1:C20 |
| criteria2 | No · repeats | Additional associated criteria. You can enter up to 127 criteria pairs."<20" |
Try SUMIFS
Sum between two dates and include the whole end day
=SUMIFS(C2:C10,B2:B10,">="&E2,B2:B10,"<"&(E3+1))E2 and E3 are numeric, date-only report parameters: November 2 through November 4, 2026, both included. Use two criteria on the same timestamp range: ">="&E2 and "<"&(E3+1). The next-day exclusive boundary includes midnight, noon and 23:59:59 on the end date. The eligible amounts are 40 + 15 + 8 - 6 + 11 + 0 = 68; the pre-start row, next-day midnight and missing date are excluded. Using "<="&E3 instead gives 63 because it misses the end-day afternoon and refund; using "<="&(E3+1) wrongly includes next-day midnight and gives 97. Numeric zero is valid, and a refund reduces the total. Column A is readable text only; B contains actual numeric timestamps. Convert and validate imported text dates before applying the formula. Keep E2 <= E3 and both parameters free of time fractions. If the upper parameter is already an exclusive timestamp, compare directly to it without adding a day; this formula does not convert time zones.
Inspect the current formula
Read its written arguments or observe actual operation values in a separate local calculation. This action neither applies a draft nor grades an answer.
Read the formula, one argument at a time
Explore the written function calls and their inputs. The calculation result belongs to the complete formula.
SUMIFS · top-level call
=SUMIFS(C2:C10,B2:B10,">="&E2,B2:B10,"<"&(E3+1))Follow the cells behind a result
Read explicit local references and helper formulas using a separate calculation. Your draft stays in this field; the worksheet and practice checks do not change.
The worksheet runs locally. Published checks apply to the supplied example only. Native Excel and Google Sheets compatibility remain unverified.
SUMIFS worked examples
Compare a regional total with an escaped-label exclusion. Read each formula alongside its source data and expected result, then open it in the worksheet.
Total paid amounts from one region
Total paid amounts from one region. The result is checked against a separately authored expected value for the supplied table. You can edit the data to explore the rule.
=SUMIFS(A2:A6,C2:C6,"East",E2:E6,"Paid")| Amount | Label | Region | Date serial | Status |
| 12 | "Harbor" | "East" | 46296 | "Paid" |
| 25 | "Cedar" | "West" | 46301 | "Pending" |
| -5 | "Harbor" | "East" | 46310 | "Paid" |
| 18 | "Lumen" | "East" | 46326 | "Paid" |
| 0 | Empty cell | "West" | 46327 | "Paid" |
Quotes distinguish text inputs from numbers; Empty cell means blank. The downloadable workbook contains the original cell values.
- Expected G2
- 25
Exclude an escaped literal label
Join <> to the active wildcard criterion in C2. The two literal A?~ spellings contribute 1 and 3, so excluding them leaves 62 from the original total of 66. Ax~ remains included. Keep the label and amount ranges aligned.
=SUMIFS(B2:B12,A2:A12,"<>"&C2)| Label | Amount | Criterion |
| "A?~" | 1 | "A~?~~" |
| "Ax~" | 2 | Empty cell |
| "a?~" | 3 | Empty cell |
| "A*" | 4 | Empty cell |
| "AB" | 5 | Empty cell |
| "~" | 6 | Empty cell |
| "~~" | 7 | Empty cell |
| "~*" | 8 | Empty cell |
| "*" | 9 | Empty cell |
| "?" | 10 | Empty cell |
| "A~~" | 11 | Empty cell |
Quotes distinguish text inputs from numbers; Empty cell means blank. The downloadable workbook contains the original cell values.
- Expected G2
- 62
Sum between two dates and include the whole end day
E2 and E3 are numeric, date-only report parameters: November 2 through November 4, 2026, both included. Use two criteria on the same timestamp range: ">="&E2 and "<"&(E3+1). The next-day exclusive boundary includes midnight, noon and 23:59:59 on the end date. The eligible amounts are 40 + 15 + 8 - 6 + 11 + 0 = 68; the pre-start row, next-day midnight and missing date are excluded. Using "<="&E3 instead gives 63 because it misses the end-day afternoon and refund; using "<="&(E3+1) wrongly includes next-day midnight and gives 97. Numeric zero is valid, and a refund reduces the total. Column A is readable text only; B contains actual numeric timestamps. Convert and validate imported text dates before applying the formula. Keep E2 <= E3 and both parameters free of time fractions. If the upper parameter is already an exclusive timestamp, compare directly to it without adding a day; this formula does not convert time zones.
=SUMIFS(C2:C10,B2:B10,">="&E2,B2:B10,"<"&(E3+1))| Date/time label (text) | Numeric timestamp | Amount | Report parameter | Date-only serial |
| "2026-11-01 23:59:59" | 46327.99998842592 | 23 | "Start date" | 46328 |
| "2026-11-02 00:00:00" | 46328 | 40 | "Inclusive end date" | 46330 |
| "2026-11-03 12:00:00" | 46329.5 | 15 | ||
| "2026-11-04 00:00:00" | 46330 | 8 | ||
| "2026-11-04 12:00:00" | 46330.5 | -6 | ||
| "2026-11-04 23:59:59" | 46330.99998842592 | 11 | ||
| "2026-11-05 00:00:00" | 46331 | 29 | ||
| "2026-11-04 08:00:00" | 46330.333333333336 | 0 | ||
| "Missing date" | Empty cell | 100 |
Quotes distinguish text inputs from numbers; Empty cell means blank. The downloadable workbook contains the original cell values.
- Expected G2
- 68
The supplied examples are checked in the browser engine. Native Excel and Google Sheets recalculation remain unverified.
Practice across worksheets
Build a linked lookup or conditional report, edit its source sheets and check the formula against other datasets.
Open cross-sheet formula practice →Practice a complete task
Reference and compatibility
Univer 1.0.4 / Apache-2.0. Calculation checks cover the supplied examples only. The browser worksheet does not implement every Excel feature, external service or connected data type.