The SUMIF function in Excel
SUMIF adds up the numbers in rows that meet one condition.
What SUMIF does
You give it the cells to test, the condition, and the cells to add. When the cells to test and the cells to add are the same, you can leave the last part out.
Syntax
=SUMIF(range, criteria, [sum_range])
- range
- The cells to test against the condition.
- criteria
- The condition, such as "North" or ">100".
- sum_range
- Optional. The cells to add. If left out, the cells in range are added.
Worked example
| A | B | C | |
|---|---|---|---|
| 1 | Product | Region | Sales |
| 2 | Lamp | North | 120 |
| 3 | Mug | South | 80 |
| 4 | Desk | North | 150 |
| 5 | Chair | East | 90 |
| 6 | Plant | North | 60 |
| 7 | North total | 330 |
Three rows are in the North region: 120 + 150 + 60 = 330.
Now you try
A fresh exercise every time. Press Another exercise as often as you like.
Where you will use it
Finance
Total the spend for one team or category.
Sales & Ops
Add up sales for a single region or product.
Data cleanup
Total only the rows above a size threshold.
Common mistakes
- Ranges that do not line up. The cells you test and the cells you add should start and end on the same rows.
- Leaving the quotes off a comparison such as ">100".
- Needing two conditions. SUMIF handles one. Use SUMIFS for several.
The SUMIF course
- 1Add up what matchesFree practice belowPractise
- 2Sum between two valuesMembers lesson
- 3Sum by monthMembers lesson
- 4Sum with wildcardsMembers lesson
- 5Budget vs actual by categoryMembers lesson
Common questions
What is the difference between SUMIF and SUMIFS?
SUMIF takes one condition. SUMIFS takes several, and in SUMIFS the range to add comes first.
Can SUMIF add numbers greater than a value?
Yes. For example =SUMIF(C2:C6,">100") adds only the figures above 100.
Related functions
Unlock every lesson
Every function has a free lesson. Members get all the lessons, fresh exercises every time and the weekly leaderboard. £7 a month or £49 a year, and you can cancel at any time.