Excel function guide · Conditional sums & counts
SUMIFS function in Excel
SUMIFS adds the numbers in one range for the rows that meet all of your conditions, such as Region is North and Product is Laptop. The range to add comes first, followed by pairs of criteria range and criterion.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
| Argument | Required? | What it means |
|---|---|---|
| sum_range | Required | The numbers to add. |
| criteria_range1 | Required | The range to test against the first condition. Same size as sum_range. |
| criteria1 | Required | The condition: a value ("North"), a cell (F2), or a comparison in quotes (">=3000"). |
| criteria_range2, criteria2, … | Optional | More pairs. A row is added only when it meets every condition. |
SUMIFS examples
Every example uses this small table. The results are what Excel shows in the cell.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Rep | Product | Sales |
| 2 | North | Aisha | Laptop | 4,200 |
| 3 | South | Marco | Laptop | 3,100 |
| 4 | North | Aisha | Monitor | 1,250 |
| 5 | North | Jonas | Laptop | 3,000 |
| 6 | South | Marco | Monitor | 900 |
| 7 | East | Priya | Laptop | 3,650 |
1. One condition
=SUMIFS(D2:D7, A2:A7, "North")
Result: 8,450
Adds the three North rows: 4,200 + 1,250 + 3,000.
2. Two conditions
=SUMIFS(D2:D7, A2:A7, "North", C2:C7, "Laptop")
Result: 7,200
Only rows where Region is North and Product is Laptop: 4,200 + 3,000.
3. A number condition
=SUMIFS(D2:D7, C2:C7, "Laptop", D2:D7, ">=3000")
Result: 13,950
Laptop sales of 3,000 or more. The comparison goes inside the quotes, and the sum range can also be a criteria range.
Try it now
Practise SUMIFS in a live sheet.
Type each formula yourself. Answers are checked by the same engine as the full course. No account needed.
Step 1 of 3
In F3, total the sales for the region in F2. Point at F2 for the criterion.
Common SUMIFS mistakes
SUMIF's argument order
SUMIF puts the range to add last; SUMIFS puts it first. Mixing them up adds the wrong column or returns #VALUE!.
Ranges of different sizes
Every criteria range must be the same size as sum_range (D2:D7 with A2:A7, not A2:A8), or SUMIFS returns #VALUE!.
Comparing with a cell
Write the operator in quotes and join the cell with &: ">="&F1. Writing ">=F1" compares with the text "F1".
Hidden differences in text
Criteria aren't case-sensitive, but spaces count: "North " with a trailing space doesn't match "North". Numbers stored as text in sum_range are ignored.
Expecting OR logic
All conditions must be true at once. For North or South, add two SUMIFS together.
Practise this in Excel Quest
SUMIFS & COUNTIFS Members
Course 5 · Conditional Aggregation · 12 min
Answer business questions that need two, three or a date range of conditions at once.
This lesson is included in Formula Quest memberships. A free account starts you on the free lessons of the Excel Quest curriculum.