Basic FunctionsIntermediate

Total a Mileage Claim for One Pay Period with SUMIFS

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.

Task:

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.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

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.

11 rows × 6 columns2 cells you fill in
ABCDEF
1DateClient visitedMilesClaim period
210/27/2026Harlow Joinery42From11/1/2026
310/30/2026Penfold Dairy67To11/15/2026
411/2/2026Ashby Print18Miles
511/4/2026Harlow Joinery42Rate per mile0.45
611/9/2026Kestrel Foods95Claim
711/12/2026Penfold Dairy67
811/15/2026Ashby Print18
911/16/2026Kestrel Foods95
1011/1/2026Morton Glass23
1111/18/2026Harlow Joinery42
What this exercise teachesMay contain the answer

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.