A mileage log never lines up neatly with the pay period: trips from last month sit above it, trips from next week sit below it, and the odd one gets logged late, out of order. Two date conditions on the same column pick out exactly the period's trips, wherever they happen to sit, and change with the dates rather than with your selection.
You are a field sales rep, and finance pays mileage twice a month. Your trip log is in A2:C11, in the order you typed it up, which is not quite the order you drove it. The claim period is in F2 and F3, from 11/1/2026 to 11/15/2026, both days included. In F4, total the miles you drove in that period, reading the dates from F2 and F3 rather than typing them into the formula. Then in F6, turn the miles into the amount to claim at the rate in F5.
Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.
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 | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Client visited | Miles | Claim period | ||
| 2 | 10/27/2026 | Harlow Joinery | 42 | From | 11/1/2026 | |
| 3 | 10/30/2026 | Penfold Dairy | 67 | To | 11/15/2026 | |
| 4 | 11/2/2026 | Ashby Print | 18 | Miles | ||
| 5 | 11/4/2026 | Harlow Joinery | 42 | Rate per mile | 0.45 | |
| 6 | 11/9/2026 | Kestrel Foods | 95 | Claim | ||
| 7 | 11/12/2026 | Penfold Dairy | 67 | |||
| 8 | 11/15/2026 | Ashby Print | 18 | |||
| 9 | 11/16/2026 | Kestrel Foods | 95 | |||
| 10 | 11/1/2026 | Morton Glass | 23 | |||
| 11 | 11/18/2026 | Harlow Joinery | 42 |
The claim period is a window on the date column, and a window has two edges, so the date column appears in SUMIFS twice: once with ">="&F2 and once with "<="&F3. SUMIFS adds a row only when every condition holds, so the two together keep exactly the trips inside the window and drop the October ones above it and the 11/16 and 11/18 trips below it. The & is what lets the conditions follow the cells. A condition is a piece of text such as ">=11/1/2026", and writing it as ">="&F2 builds that text from whatever date is in F2, so next fortnight you change two cells and the formula is right again. Both edges include their own day here because the period runs from the 1st to the 15th inclusive: the Ashby Print trip on 11/15 belongs in the claim, and with "<" in place of "<=" it would fall out and the total would come to 245 instead of 263. The Morton Glass trip on 11/1 is the reason not to select a block of rows and SUM it: it was logged last, at the bottom of the list, and a range chosen by eye would miss it, while SUMIFS judges each row by its date wherever it sits. That gives 263 miles, and at 0.45 a mile, F4*F5 is a claim of 118.35.