SheetsdrillSUMIFS
SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]) adds the cells in sum_range where every criteria pair is satisfied, so it is a SUM with AND conditions. The sum range comes first, unlike SUMIF.
Syntax
=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])| Part | What it is |
|---|---|
sum_range | The numbers to add. |
criteria_range1 | The first column to test, same height as sum_range. |
criterion1 | The condition for that column: "Ana", ">1000", "<>Cancelled", E1, or ">="&DATE(2026,1,1). |
criteria_range2, criterion2 | More pairs; all must be met. |
Examples
=SUMIFS(D2:D100, A2:A100, "Ana", B2:B100, "Mar")Result: Ana's total in March
=SUMIFS(D:D, C:C, "Won", D:D, ">=5000")Result: The sum of won deals of 5000 or more
The sum range can also be a criteria range.
=SUMIFS(D:D, A:A, ">="&DATE(2026,1,1), A:A, "<"&DATE(2026,4,1))Result: Revenue dated in Q1 2026
Common mistakes
- Putting the sum range last (SUMIF order) gives a wrong result or an error; in SUMIFS it is the first argument.
- Criteria ranges of different sizes give #VALUE! Array arguments to SUMIFS are of different size; use the same rows everywhere.
- A date typed as text in the criterion (">=1/1/2026") depends on locale; build it with DATE and the & operator.
Practice SUMIFS
These drills check your answer on live data, free, no account needed.
- Sales for one rep and monthAdd up the amounts for the rep named in F1 in the month in F2. Point at F1 and F2 instead of typing the values. Answer in F3.
- Won deals above a minimumAdd up the value of deals in the "Won" stage that are worth more than the minimum in F1. Answer in F2.
Questions
How do I SUMIFS between two dates?
Use two criteria on the same date column: ">="&start and "<="&end, where start and end are cells or DATE() values.
Does SUMIFS work with wildcards?
Yes, * for any characters and ? for one: =SUMIFS(C:C, A:A, "*Inc").
Related
- SUMIFHow SUMIF works in Google Sheets: add up values where another column matches text, a number, a date or a wildcard, with examples and the mistakes that give 0.
- COUNTIFSHow COUNTIFS in Google Sheets counts rows where several conditions are true, with examples for status and priority, date ranges and not-equal, plus errors.
- SUMPRODUCTHow SUMPRODUCT in Google Sheets multiplies two ranges row by row and adds the results, and how it works as a conditional sum with OR logic, with examples.
- QUERYHow the Google Sheets QUERY function works: the SQL-like syntax, select and where, group by with sum and count, order by, limit, and the errors people hit.