How to Use SUMIFS in Excel
SUMIFS adds the values in a range that meet one or more criteria. The first argument is the range to sum, followed by pairs of criteria range and criteria. In financial models it rolls up transaction level data into summary lines, totaling revenue by period and segment or costs by account and cost center without a pivot table.
Syntax and argument order
The signature is =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). Note the order: the sum_range comes first, unlike SUMIF where it comes last. Mixing these up is the single most common SUMIFS error.
Each criteria_range must be the same size as the sum_range. Criteria can be a value, a cell reference, or a logical expression in quotes such as ">=1000" or "<>"&0. Multiple criteria pairs are combined with AND logic, so a row must satisfy every condition to be included in the sum.
Worked example: revenue by region and quarter
Suppose a transaction tab has amounts in D2:D200, region labels in B2:B200, and quarter labels in C2:C200. You want total revenue for the West region in Q2.
- Enter
=SUMIFS(D2:D200, B2:B200, "West", C2:C200, "Q2"). - D2:D200 is the range to sum. B2:B200 is checked against "West" and C2:C200 against "Q2".
- Only rows where both region equals West and quarter equals Q2 are summed.
- Replace the literal "West" and "Q2" with cell references like H1 and H2 so the summary table refreshes as you change the row and column headers.
| Region (B) | Quarter (C) | Amount (D) |
|---|---|---|
| West | Q2 | 12000 |
| East | Q2 | 9000 |
| West | Q2 | 8000 |
| West | Q1 | 5000 |
Only rows 1 and 3 match West and Q2, so the result is 20000.
Criteria expressions that earn their keep
Comparison operators let you sum ranges of values. To total deals at or above a threshold, use =SUMIFS(D2:D200, D2:D200, ">=10000"), where the sum range and criteria range are the same column.
To reference a threshold cell, concatenate the operator and the reference: ">="&H1. Wildcards work on text criteria, so "West*" matches West, Western, and West Coast. A date window uses two criteria pairs on the same date column, one ">="&start and one "<="&end.
Common pitfalls in models
Argument order is the top mistake: SUMIFS puts the sum_range first, SUMIF puts it last. If your total looks wrong, check this first.
Mismatched range sizes throw a #VALUE! error. Every criteria_range must span exactly the same rows as the sum_range. Avoid full column references mixed with bounded ones in older files for the same reason.
Text criteria are not case sensitive, so "west" and "West" sum together, which is usually fine but worth knowing. For multiple OR conditions on one field, SUMIFS alone cannot do it, so sum several SUMIFS together or restructure with a helper column.
Find Hardcodes
Find Hardcodes flags literal criteria buried inside SUMIFS so you can move thresholds and labels into proper assumption cells.
Get ModelMint See how it worksFAQ
What is the difference between SUMIF and SUMIFS?
SUMIF supports one condition and puts the sum range last: =SUMIF(criteria_range, criteria, sum_range). SUMIFS supports multiple conditions and puts the sum range first: =SUMIFS(sum_range, criteria_range1, criteria1, ...). For models, standardize on SUMIFS so the argument order is consistent.
How do I use a date range in SUMIFS?
Apply two criteria pairs to the same date column. For example, =SUMIFS(D2:D200, A2:A200, ">="&H1, A2:A200, "<="&H2) sums amounts whose date falls between the start in H1 and the end in H2 inclusive.
Can SUMIFS handle OR conditions?
Not directly, because all criteria pairs are combined with AND. To total rows matching value A or value B in one field, add two SUMIFS results together, or use SUMPRODUCT, or add a helper column that flags either condition.