Travel Claims by Department
One total and one count, and both need to agree on exactly the same rows.
Task:
You're in Finance closing out last month's expense claims. Someone in Sales management wants to know how much the team spent on travel, and how many claims made up that figure. Using the Department and Category columns, put the total in B10 and the number of matching claims in B11.
Learning Objectives:
- Total a range against two conditions at once with SUMIFS
- Count the rows behind that total using the same criteria in COUNTIFS
- See why criteria pairs are matched row by row rather than filtered independently
Solve without hints for +5 XP
Interactive Spreadsheet
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 | C | D | |
|---|---|---|---|---|
| 1 | Employee | Department | Category | Amount |
| 2 | Alicia Munroe | Sales | Travel | 412.5 |
| 3 | Devon Park | Sales | Meals | 68.2 |
| 4 | Priya Anand | Marketing | Travel | 205 |
| 5 | Tom Reyes | Sales | Travel | 189.75 |
| 6 | Lena Fischer | Marketing | Meals | 42.1 |
| 7 | Sam Okoro | Sales | Software | 99 |
| 8 | Grace Whitfield | Sales | Travel | 310.4 |
| 9 | Nadia Volkov | Marketing | Travel | 150 |
| 10 | Sales Travel total | |||
| 11 | Sales Travel claims |
What this exercise teaches (contains the answer)
SUMIFS and COUNTIFS test every criteria pair against the same row before it counts, which is why a Marketing claim tagged Travel or a Sales claim tagged Software never sneaks into either result — both conditions have to hold on one employee's row, not just somewhere in each column. Giving COUNTIFS the identical pair of ranges and criteria as SUMIFS, rather than a different-looking filter, is what keeps the total and the claim count guaranteed to describe exactly the same rows.