SUMIFS Generator
Add up values only on the rows that satisfy every condition you set.
SUMIFS
Add up values only on the rows that satisfy every condition you set.
The numbers to add. In SUMIFS this argument comes first, unlike SUMIF.
Column checked against the first condition. Must be the same size as the sum range.
Condition for the first range. Text and comparisons need quotes; cell references do not.
Column checked against the second condition. Repeat the same range to build a between test.
Condition for the second range, for example ">100" or "<>Cancelled".
=SUMIFS(C2:C100, A2:A100, "West", B2:B100, ">100")Worked Example
A sales log with region in column A, units in column B, and order value in column C, and you want West orders over 100 units.
=SUMIFS(C2:C100, A2:A100, "West", B2:B100, ">100")Returns: Adds the column C values only for rows where the region is West AND units are greater than 100. Rows that meet just one of the two conditions contribute nothing.
Checks Before You Paste
- •The sum range comes FIRST in SUMIFS, the exact opposite of SUMIF where it is the optional last argument. Copying a working SUMIF and bolting an extra range onto the end is the most common way to get a silently wrong total.
- •Every criteria range must have the same height and width as the sum range. Pairing C2:C100 with A2:A101 returns #VALUE! in Excel and a mismatched-range error in Google Sheets.
- •To compare against a cell instead of a typed value, join the operator to the reference with &: ">"&F1. Writing ">F1" compares each value against the literal text F1 rather than the number in F1, which normally returns 0 with no warning.
How SUMIFS works
Availability: Excel 2007 and later on Windows and Mac, Excel for the web, Google Sheets and LibreOffice Calc. Workbooks that must open in Excel 2003 need SUMPRODUCT instead.
SUMIFS adds the values in one range on the rows where every condition you give it holds. Its arguments start with the sum range and then run in pairs - criteria range, criterion, criteria range, criterion - up to 127 pairs. That leading sum range is the opposite of SUMIF, where the sum range is the optional third argument, and taking a working SUMIF and appending an extra range to it is the single most common way to produce a total that is wrong without being an error.
Every criterion is AND-ed. There is no argument, and no ordering, that turns any of them into an OR: a row contributes to the total only if it satisfies all of them. Two conditions on the same column are therefore how you express a range - criteria_range1 and criteria_range2 can both be B2:B500, one testing ">=100" and the other "<=500", giving a between test. Conditions that should be OR-ed need two SUMIFS added together, or an array constant: =SUM(SUMIFS(D2:D500, B2:B500, {"West","North"})) runs the function once per region and adds the pair.
The criteria grammar is the same as SUMIF's. A bare value matches on equality, case-insensitively. Quoted comparisons such as ">100" and "<>Cancelled" apply an operator. Wildcards * and ? work in text criteria, escaped with ~. To compare against a cell the operator must be joined on with an ampersand - ">="&$F$1 - because ">=F1" is the literal text F1 and matches nothing, returning a silent zero.
Unlike SUMIF, SUMIFS does not resize anything. Every criteria range has to be the same height and width as the sum range; pair C2:C500 with A2:A501 and Excel returns #VALUE! rather than guessing which rows you meant. That strictness is a feature - it turns a class of silent miscalculations into a visible error - and it is a reason to prefer SUMIFS even when there is only one condition. Mixing a bounded range with a whole-column reference such as A:A trips the same check.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)| Argument | Required | What it does |
|---|---|---|
sum_range | Required | The cells actually added. It comes first, and it sets the shape every criteria range must match. Text and blanks in it are skipped rather than raising an error. |
criteria_range1 | Required | The first range to test, the same height and width as sum_range. May be the same range as sum_range when the condition is on the values being added. |
criteria1 | Required | The first condition. A value, a reference, or quoted text with an operator and the wildcards * ? ~. Joined to a cell with & when you need an operator plus a reference. |
criteria_range2, criteria2 | Optional | Further range-and-condition pairs, up to 127 of them. All are AND-ed; repeating the same range with two comparisons is how a between test is written. |
More worked examples
A transactions sheet with dates in A2:A2000, department in B2:B2000 and amounts in D2:D2000. You want the Marketing spend for the first quarter of 2026.
=SUMIFS(D2:D2000, B2:B2000, "Marketing", A2:A2000, ">="&DATE(2026,1,1), A2:A2000, "<="&DATE(2026,3,31))Returns: The total of column D on Marketing rows dated within Q1 2026.
The date column appears twice, once for each end of the window. DATE() rather than a typed "01/01/2026" keeps the formula working on a machine with US date settings.
An order book with statuses in C2:C900 and values in E2:E900, where you want everything that is not cancelled and not blank.
=SUMIFS(E2:E900, C2:C900, "<>Cancelled", C2:C900, "<>")Returns: The value of every order with a status other than Cancelled, ignoring unstatused rows.
"<>Cancelled" alone would include the blanks, since an empty cell is not the word Cancelled. The second pair is what excludes them.
Supplier invoices with names in A2:A600 and amounts in C2:C600, totalling everything from any Northgate entity above 1,000.
=SUMIFS(C2:C600, A2:A600, "Northgate*", C2:C600, ">1000")Returns: The combined value of Northgate invoices over 1,000.
The second pair tests the sum range against itself, which is legal and often the clearest way to filter on the amounts you are adding.
Common mistakes
- Argument order inherited from SUMIF
- SUMIF is range, criteria, sum_range; SUMIFS is sum_range first. Writing =SUMIFS(A2:A100,"West",D2:D100) tests the region column for the word West as a range and returns #VALUE! at best, or a wrong number when the shapes happen to line up.
- Expecting criteria to be OR-ed
- =SUMIFS(D2:D500, B2:B500, "West", B2:B500, "North") always returns 0, because no single row can be both. For an OR, add two SUMIFS or wrap an array constant in SUM.
- Ranges of different heights
- Every criteria range must match sum_range exactly. The usual cause is one range extended to a new row and the others left behind, or one whole-column reference mixed in with bounded ones. Excel returns #VALUE! rather than resizing, as SUMIF would.
- Operators typed inside the quotes with a cell name
- ">F1" compares against the text F1 and matches nothing, so the total comes back 0 with no error at all. Concatenate instead: ">"&F1. The same applies to dates, where DATE(2026,1,1) is safer than a typed date string.
- Criteria that look numeric but are not
- ">1000" only matches real numbers. If part of the amounts column was imported as text those rows silently contribute nothing, so the total is short by exactly the text rows. Check with =COUNT(C2:C600) against =COUNTA(C2:C600).
Frequently Asked Questions
You cannot inside one call - every pair is AND-ed. Either add two calls together, or pass an array constant and wrap it: =SUM(SUMIFS(D2:D500, B2:B500, {"West","North"})). The array form runs SUMIFS once per item and SUM collapses the results, which keeps it to a single cell.
Yes - list the date column twice, once with ">="&start and once with "<="&end. Excel stores dates as numbers, so the comparison operators work directly on them. Reference DATE() or a cell rather than typing a date string, which is interpreted using the machine's regional settings.
Because zero is the true sum of an empty set. That makes a genuine zero total and a broken criterion look identical, so when a SUMIFS result is unexpectedly 0, test the criteria with COUNTIFS first - a count of 0 means the match is failing, a count above 0 means the amounts are the problem.
127 criteria pairs, which no real report reaches. The practical limit is speed: each pair scans the full range, so a dozen conditions over whole-column references recalculates noticeably. Bound the ranges to the rows in use, or reference an Excel Table column that grows with the data.
SUMIFS when the answer feeds other formulas, when the layout is fixed, or when the figure must refresh instantly. A PivotTable when you want to explore the breakdown and change the grouping - it is faster to build for many combinations at once, but it needs a manual refresh after the source data changes.
Related Tools
SUMIF Generator
The single-condition version, with the arguments the other way round
COUNTIFS Generator
Count matching rows instead of adding their values
SUMPRODUCT Generator
Conditional totals SUMIFS cannot express, including OR logic
Pivot Table Generator
Summarise the same data by every combination at once