SUMIFS practice exercises
SUMIFS adds up the numbers in one column, but only for the rows that meet every condition you give it. It answers questions like "What were Gadget sales in the East?" without filters or pivot tables.
Syntax
SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
sum_range- The numbers to add. In SUMIFS it comes first (in SUMIF it comes last).
criteria_range1- The column to test.
criterion1- The condition: a value like
"East"or a comparison like">100".
The practice data
An orders table: who sold what, where, and for how much. Every exercise below uses this sheet, and your answer goes in H2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Product | Units | Price | Sales |
| 2 | Ana | East | Widget | 12 | 5 | 60 |
| 3 | Ben | West | Gadget | 5 | 20 | 100 |
| 4 | Cara | East | Gadget | 8 | 20 | 160 |
| 5 | Dev | North | Widget | 20 | 5 | 100 |
| 6 | Ana | West | Gizmo | 3 | 50 | 150 |
| 7 | Ben | East | Widget | 7 | 5 | 35 |
| 8 | Cara | North | Gizmo | 2 | 50 | 100 |
| 9 | Dev | East | Gadget | 6 | 20 | 120 |
Worked example
=SUMIFS(F2:F9,B2:B9,"East",C2:C9,"Gadget")
Adds the Sales of rows where Region is East and Product is Gadget. Result:
| 280 |
Your turn: 3 exercises
Type a formula and press Enter. It is checked by a real formula engine, so any correct approach counts.
1 Return the total Sales for rep Ana.
2 Return the total Units of Widget sold in the East.
3 Return the total Sales of orders over 100.
Master SUMIFS in the full course
Lesson 6 of 18 goes deeper, with more exercises in a real spreadsheet grid. Start with the first 4 lessons free, then unlock everything with Pro.
Start free See pricingCommon SUMIFS mistakes
- Putting sum_range last out of habit from SUMIF. In SUMIFS it is always the first argument.
- Writing
>100without quotes. Criteria with operators must be text:">100", or">"&H1to use a cell. - Ranges of different sizes, which gives an error.
FAQ
What is the difference between SUMIF and SUMIFS?
SUMIF takes one condition and puts the sum range last. SUMIFS takes any number of conditions and puts the sum range first.
Can SUMIFS use OR logic?
Not directly. Add two SUMIFS together, for example East plus West.