# Get last value entered past a specific row

I have been on this a couple of days and I cannot get my head around this ... I use a spreadsheet to track the number of miles traveled by drivers and I want to work out how many miles a driver has traveled between 2 specified dates. Here is the spreadsheet. Now using this formula I can easily find out the milages I start with.

``````= MIN(FILTER('Sheet1'!C2:C, 'Sheet1'!A2:A >= START_DATE, 'Sheet1'!A2:A <= END_DATE))
``````

However, if I use a similar formula for the milages traveled up to the END_DATE I do not get the piece of information I need.

``````= MAX(FILTER('Sheet1'!C2:C, 'Sheet1'!A2:A >= START_DATE, 'Sheet1'!A2:A <= END_DATE))
``````

So let's say I want to find out how many miles the driver traveled between June 18th and June 24 the first formula return the correct value

``````1690
``````

However, the second one returns the value

``````1772
``````

without taking account the miles traveled the late shift. Is there a way to get that entry?

Many thanks for all your inputs!

## 1 Answer

``````=ARRAYFORMULA(MAX(QUERY({IF(ROW(Sheet1!A2:A) <= MAX(IF(NOT(ISBLANK(Sheet1!B2:B)),
ROW(Sheet1!A2:A))), VLOOKUP(ROW(Sheet1!A2:A), FILTER({ROW(Sheet1!A2:A), Sheet1!A2:A},
LEN(Sheet1!A2:A)), 2), ), Sheet1!C2:C},
"select Col2
where Col1 >= "&DATEVALUE(F1)&"
and Col1 <= "&DATEVALUE(F2), 0)))`````` • wow, that was quick! and that is only to work out the max value of the END_DATE, correct? – Danny Jul 28 '19 at 18:54
• @Danny you can use it for MIN too if you change MAX to MIN like: `=ARRAYFORMULA(MIN(QUERY({IF(ROW(Sheet1!A2:A) <= MAX(IF(NOT(ISBLANK(Sheet1!B2:B)), ROW(Sheet1!A2:A))), VLOOKUP(ROW(Sheet1!A2:A), FILTER({ROW(Sheet1!A2:A), Sheet1!A2:A}, LEN(Sheet1!A2:A)), 2), ), Sheet1!C2:C}, "select Col2 where Col1 >= "&DATEVALUE(F1)&" and Col1 <= "&DATEVALUE(F2), 0)))` – user0 Jul 28 '19 at 18:57
• awesome! but I am having some issue with my date as on the reporting sheet it is display as "dd/mm". is there a conversion I can apply before handing it to the DATEVALUE function? – Danny Jul 28 '19 at 19:03
• it was super helpful! just needed a tiny tweak due to the formatting of the date, thank you ever so much! – Danny Jul 28 '19 at 19:51
• much appreciated! – Danny Jul 28 '19 at 21:07