SUMIFS - Total rows that meet every rule
Combine department, approval status, and amount rules in a reliable expense total.
By the end, you can
- Put the amount range first in SUMIFS.
- Combine conditions using AND logic.
- Use inclusive lower and upper bounds.
Before you start: Understand SUMIF criteria and aligned ranges.
In this lesson
A total with more than one requirement
Finance needs approved Sales reimbursements. Being in Sales alone is not enough, and being approved alone is not enough. SUMIFS adds an amount only when every supplied condition is true for that record. It is useful for regional channel sales, approved department spending, and amounts within a reporting band.
Syntax and arguments
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Unlike SUMIF, SUMIFS starts with the amounts. Each condition is a range-and-criterion pair.
| Argument | Purpose | Example |
|---|---|---|
sum_rangeRequired | Numeric amounts to total. | C2:C6 |
criteria_range1, criteria1Required | The first test column and its condition. | A2:A6, "Sales" |
additional pairsOptional | More aligned test ranges and conditions, up to 127 pairs. | B2:B6, "Approved" |
Approved Sales reimbursements
₹7,000| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Department | Status | Claim | Sales approved | ||
| 2 | Sales | Approved | ₹3,500 | ₹7,000 | ||
| 3 | Support | Approved | ₹2,400 | |||
| 4 | Sales | Pending | ₹1,800 | |||
| 5 | Sales | Approved | ₹2,500 | |||
| 6 | Sales |
Change the reporting question
=SUMIFS(C2:C6,B2:B6,"Approved")One pair is also valid. Add all four approved claims.
=SUMIFS(C2:C6,B2:B6,"Approved",C2:C6,">=2500")The amount column can also be a test column. The ₹3,500 and ₹2,500 approved claims qualify.
=SUMIFS(C2:C6,C2:C6,">=2000",C2:C6,"<=3000")Apply two conditions to one column. ₹2,400 and ₹2,500 fall inside the inclusive band.
All rules must pass on the same record
- Criteria pairs use AND logic. Adding a condition can remove matching rows; it does not create an OR choice.
- The sum range and every criteria range must have matching dimensions, or Excel can return #VALUE!.
- No qualifying amounts returns 0. Text matching ignores letter case, but extra spaces can change labels.
- Use quoted comparisons and concatenate a cell-held threshold with its operator. A repeated criteria range is valid for lower and upper bounds.
Common mistakes and fixes
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.