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 |
C7 =SUMIF(B2:B6,"North",C2:C6)
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 valuesComing soon
- 3Sum by monthComing soon
- 4Sum with wildcardsComing soon
- 5Budget vs actual by categoryComing soon
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.