SUMIFS: adding up the rows that match
SUMIFS adds up the numbers on the rows that match all of your conditions, such as the sales in the North region in February. It's the quickest way to get a total for one group out of a long list.
A worked example
Here's a list of sales, with the region and month for each.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Month | Sales |
| 2 | North | Jan | £410.00 |
| 3 | South | Feb | £275.00 |
| 4 | North | Feb | £320.00 |
| 5 | East | Jan | £190.00 |
| 6 | North | Feb | £450.00 |
| 7 | South | Jan | £360.00 |
To find the total for North in February by hand:
- Go down the Region column and pick out the North rows: rows 2, 4 and 6.
- Of those, keep the ones where the month is Feb: rows 4 and 6.
- Add up their sales: £320 + £450 = £770.
SUMIFS does the picking and the adding in one step.
The formula
=SUMIFS(C2:C7,A2:A7,"North",B2:B7,"Feb")
C2:C7is what to add up. With SUMIFS this always comes first.A2:A7,"North"is the first condition: where to look, then what to look for.B2:B7,"Feb"is the second condition. Add as many pairs as you need. A row is only added if it matches every one.
It's better to type the region and month into cells and point to those, so you can change them without editing the formula:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Month | Sales | Region | Month | Total | |
| 2 | North | Jan | £410.00 | North | Feb | £770.00 | |
| 3 | South | Feb | £275.00 | ||||
| 4 | North | Feb | £320.00 | ||||
| 5 | East | Jan | £190.00 | ||||
| 6 | North | Feb | £450.00 | ||||
| 7 | South | Jan | £360.00 |
Conditions with more than, less than and dates
Put a comparison in quotes: ">500" means more than 500. To compare with a number in a cell, keep the comparison in quotes and join the cell on with &:
=SUMIFS(C2:C7,C2:C7,">="&H1)
Dates work the same way. Sales from 1 March 2026 onwards, with the dates in column D, would be =SUMIFS(C2:C7,D2:D7,">="&DATE(2026,3,1)).
SUMIF or SUMIFS?
SUMIF handles one condition, and puts the range to add up last: =SUMIF(A2:A7,"North",C2:C7). SUMIFS puts it first. Mixing the two orders up is the most common SUMIFS mistake. SUMIFS works with a single condition too, so you can use it every time and only learn one order.
Common mistakes
#VALUE!: the ranges are different sizes
Every range in SUMIFS must cover the same rows. C2:C7 with A2:A8 gives #VALUE!.
A total of 0 when you compare with a cell
">=H1" inside quotes is just text, so Excel compares with the letters H1 and finds nothing. Use ">="&H1.
Rows missing from the total
Check for spaces at the end of labels ("North ") and numbers stored as text. Both look right but don't match. The case of letters doesn't matter: "north" matches "North".
Try it now
In G2, add up the sales for the region in E2 and the month in F2. Point to E2 and F2 rather than typing South and Feb.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Month | Sales | Region | Month | Total | |
| 2 | North | Jan | £410.00 | South | Feb | ||
| 3 | South | Feb | £275.00 | ||||
| 4 | North | Feb | £320.00 | ||||
| 5 | East | Jan | £190.00 | ||||
| 6 | South | Feb | £505.00 | ||||
| 7 | North | Feb | £450.00 | ||||
| 8 | South | Jan | £360.00 | ||||
| 9 | East | Feb | £230.00 | ||||
| 10 | South | Feb | £145.00 | ||||
| 11 | North | Jan | £280.00 | ||||
| 12 | South | Jan | £95.00 |
Where it works
SUMIFS works in Excel 2007 onwards, Google Sheets and LibreOffice Calc. AVERAGEIFS, COUNTIFS, MAXIFS and MINIFS take their conditions in the same way.