FormulaGym
XP 0
Streak 0

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

ABC
1ProductRegionSales
2LampNorth120
3MugSouth80
4DeskNorth150
5ChairEast90
6PlantNorth60
7North total330
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

  1. 1
    Add up what matchesFree practice below
    Practise
  2. 2
    Sum between two valuesComing soon
  3. 3
    Sum by monthComing soon
  4. 4
    Sum with wildcardsComing soon
  5. 5
    Budget 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.

Related functions