How to sum cells that meet a condition in Excel
SUMIFS adds up the numbers in one column, but only on rows where other columns match your conditions. It handles one condition or many.
The column to add comes first, then each pair of a range to check and what to look for.
The formula
| A | B | C | |
|---|---|---|---|
| 1 | Region | Rep | Sales |
| 2 | North | Amara | 400 |
| 3 | South | Ben | 250 |
| 4 | North | Chloe | 150 |
| 5 | West | Dev | 700 |
=SUMIFS(C2:C5,A2:A5,"North")Result: 550
Adds the sales on the two North rows: 400 + 150.
Step by step
- Type =SUMIFS( and select the column to add.
- Add a comma, select the column to check, and type the condition in quotes.
- Add more range and condition pairs if you need them.
- Close the bracket and press Enter.
Other ways to write it
=SUMIF(A2:A5,"North",C2:C5)The older one-condition version. Note that the range to add comes last.=SUMPRODUCT((A2:A5="North")*C2:C5)Handles conditions SUMIFS can’t, such as calculated values.
Each of these gives the same result on the example above, in Excel and in Cellwise.
Common mistakes
- Typing the condition into the formula
- Put the region in a cell and point at it. Then the same formula answers for any region.
- Mixing up SUMIF and SUMIFS
- SUMIFS puts the range to add first, while SUMIF puts it last.
Reading isn’t the same as doing
Totals with conditions gives you real tasks on this, and tests your formulas on data you haven’t seen. It’s free, with no sign-up.