FormulaGym
XP 0
Streak 0

The SUMIFS function in Excel

SUMIFS adds up the numbers in rows that pass several tests at once.

What SUMIFS does

It works like SUMIF with extra conditions. The important difference is the order: in SUMIFS the cells to add come first, then each pair of cells to test and the condition to apply.

Syntax

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
sum_range
The cells to add. In SUMIFS this comes first.
criteria_range1
The first set of cells to test.
criteria1
The first condition, such as "North".
criteria_range2, criteria2
Optional. More pairs. A row must pass every pair.

Worked example

ABC
1ProductRegionSales
2LampNorth120
3MugSouth80
4DeskNorth150
5ChairEast90
6PlantNorth60
7North over 100270
C7 =SUMIFS(C2:C6,B2:B6,"North",C2:C6,">100")

Two North rows have sales above 100: 120 + 150 = 270.

Now you try

A fresh exercise every time. Press Another exercise as often as you like.

Where you will use it

Finance

Total spend for one team in one month.

Sales & Ops

Add up sales by region and product.

HR

Total hours for one department above a threshold.

Common mistakes

  • Putting the range to add last, as SUMIF does. In SUMIFS it must come first.
  • Ranges of different sizes, which causes a #VALUE! error.
  • Leaving the quotes off a comparison such as ">100".

The SUMIFS course

  1. 1
    Add up with two conditionsFree practice below
    Practise
  2. 2
    Sum between two datesComing soon
  3. 3
    Sum by region and productComing soon
  4. 4
    Wildcards in SUMIFSComing soon
  5. 5
    Budget vs actual by monthComing soon
  6. 6
    Common SUMIFS mistakesComing soon

Common questions

Does SUMIFS need every condition to be true?

Yes. A row is added only if it passes all the tests.

Can I use a cell reference in the criteria?

Yes. Join a comparison and a cell with an ampersand, for example ">"&E1.

Related functions