SUMIF with a comparison
Add up only the values above a line — the criterion can be a comparison, not just a match.
Task:
Anything over 500 needs a second signature. In B10 give the total value of the expenses over 500, and in B11 the total of the rest.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of SUMIF
0/4The same formula in the other shapes it takes at work.
- SUM with Multiple Values
Sum values from multiple non-adjacent cells or ranges.
CoreIntermediate - SUMIF: total one category
Add up only the rows that belong to the group you were asked about.
Intermediate - SUMIFS: total with two conditions
One region and one product at the same time.
Advanced - SUMIF with a comparison
Add up only the values above a line — the criterion can be a comparison, not just a match.
Intermediate
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | |
|---|---|---|
| 1 | Expense | Amount |
| 2 | Laptop | 1180 |
| 3 | Train | 86 |
| 4 | Hotel | 540 |
| 5 | Software | 499 |
| 6 | Taxi | 32 |
| 7 | Conference | 750 |
| 8 | Lunch | 64 |
| 9 | ||
| 10 | Over 500 | |
| 11 | 500 or less |
What this exercise teachesMay contain the answer
Using > and <= as the pair means a value of exactly 500 lands in one total and only one. Splitting at > and < would drop it from both, and the two figures would no longer add up to the whole — always worth checking.