Learning Excel: SUMIFS
Several conditions
"What did Sophie sell in clothing? And how much of that clothing cost more than £5?" Two conditions at once: SUMIFS and COUNTIFS.
COUNTIFS
COUNTIFS counts with several conditions at once: each time a range and a criterion. Only the rows where all the conditions hold are counted.
=COUNTIFS(B2:B16,"Clothing",C2:C16,">5") counts the clothing over £5: three items.
=COUNTIFS(B2:B16,"Clothing",C2:C16,">5")
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Item | Category | Price | Day | Volunteer | Limit | |||
| 2 | Dress green | Clothing | £12.00 | Mon | Sophie | £10.00 | 3 | ||
| 3 | Book fiction | Books | £2.00 | Mon | Omar | ||||
| 4 | Lamp | Household | £8.00 | Tue | Sophie | ||||
| 5 | T-shirt S | Clothing | £3.00 | Tue | Grace | ||||
| 6 | Dressing gown | Clothing | £6.00 | Wed | Omar | ||||
| 7 | Puzzle | Toys | £4.00 | Wed | Grace | ||||
| 8 | Book cooking | Books | £3.00 | Thu | Sophie | ||||
| 9 | Radio | Electrical | £15.00 | Thu | Omar | ||||
| 10 | T-shirt XL | Clothing | £4.00 | Fri | Sophie | ||||
| 11 | Dress black | Clothing | £18.00 | Fri | Grace | ||||
| 12 | What? Game | Toys | £5.00 | Sat | Omar | ||||
| 13 | Vase | Household | £7.00 | Sat | Grace | ||||
| 14 | T-shirt M | Clothing | £3.00 | Sat | Sophie | ||||
| 15 | Bookend | Household | £9.00 | Sat | Omar | ||||
| 16 | Kids' bike | Toys | £35.00 | Sat | Grace | ||||
| 17 |
SUMIFS, sum range first
SUMIFS adds up with several conditions. Watch out: in SUMIFS the sum range comes first, in SUMIF it comes last.
=SUMIFS(C2:C16,B2:B16,"Clothing",E2:E16,"Sophie") adds up the prices of the clothing Sophie sold: £19.
=SUMIFS(C2:C16,B2:B16,"Clothing",E2:E16,"Sophie")
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Item | Category | Price | Day | Volunteer | Limit | |||
| 2 | Dress green | Clothing | £12.00 | Mon | Sophie | £10.00 | 19 | ||
| 3 | Book fiction | Books | £2.00 | Mon | Omar | ||||
| 4 | Lamp | Household | £8.00 | Tue | Sophie | ||||
| 5 | T-shirt S | Clothing | £3.00 | Tue | Grace | ||||
| 6 | Dressing gown | Clothing | £6.00 | Wed | Omar | ||||
| 7 | Puzzle | Toys | £4.00 | Wed | Grace | ||||
| 8 | Book cooking | Books | £3.00 | Thu | Sophie | ||||
| 9 | Radio | Electrical | £15.00 | Thu | Omar | ||||
| 10 | T-shirt XL | Clothing | £4.00 | Fri | Sophie | ||||
| 11 | Dress black | Clothing | £18.00 | Fri | Grace | ||||
| 12 | What? Game | Toys | £5.00 | Sat | Omar | ||||
| 13 | Vase | Household | £7.00 | Sat | Grace | ||||
| 14 | T-shirt M | Clothing | £3.00 | Sat | Sophie | ||||
| 15 | Bookend | Household | £9.00 | Sat | Omar | ||||
| 16 | Kids' bike | Toys | £35.00 | Sat | Grace | ||||
| 17 |
Try it yourself
What did the clothing that Grace sold bring in? Work it out in I2 with SUMIFS.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Item | Category | Price | Day | Volunteer | Limit | ||||
| 2 | Dress green | Clothing | £12.00 | Mon | Sophie | £10.00 | ||||
| 3 | Book fiction | Books | £2.00 | Mon | Omar | |||||
| 4 | Lamp | Household | £8.00 | Tue | Sophie | |||||
| 5 | T-shirt S | Clothing | £3.00 | Tue | Grace | |||||
| 6 | Dressing gown | Clothing | £6.00 | Wed | Omar | |||||
| 7 | Puzzle | Toys | £4.00 | Wed | Grace | |||||
| 8 | Book cooking | Books | £3.00 | Thu | Sophie | |||||
| 9 | Radio | Electrical | £15.00 | Thu | Omar | |||||
| 10 | T-shirt XL | Clothing | £4.00 | Fri | Sophie | |||||
| 11 | Dress black | Clothing | £18.00 | Fri | Grace | |||||
| 12 | What? Game | Toys | £5.00 | Sat | Omar | |||||
| 13 | Vase | Household | £7.00 | Sat | Grace | |||||
| 14 | T-shirt M | Clothing | £3.00 | Sat | Sophie | |||||
| 15 | Bookend | Household | £9.00 | Sat | Omar | |||||
| 16 | Kids' bike | Toys | £35.00 | Sat | Grace | |||||
| 17 |
Ready for more?
The full lesson has 5 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson