# Student Pilot Spreadsheet - Retrieving date of last landing

Overview

As a student pilot I try to keep record of all my flights, total hours, costs and number of landings in Google Sheets. As a safety measure our aviation safety agency (EASA) requires pilots to perform a minimum of 3 take-offs and landings within 90 days in order for them to take passengers on a flight.

Question

Even though I fly very regularly, I would still like to know exactly how many days I have left since the last time I've performed 3 take-off's and landings.

In my spreadsheet I have a column for the date of each flight and a column which sums up the amount of landings that were made (as shown in the extract below).

Aim

My goal is to count column D from the bottom to top until the value sums up 3 and read back the related date on the same row (in red).

What have I done so far?

I have been experimenting with COUNTIF, ARRAY, INDEX, SUMIF and a combination of them but I'm out of clues. That is why I am reaching out to you.

The best I came up with (Q42 is a cell with value '3' in it). Unfortunately it leaves much to be desired:

``````=COUNTIF(ArrayFormula (SUMIF (ROW(D2:D19); "<=" &ROW(D2:D19); A2:A19)); "<=" &Q42)
``````

Use `sumif()` and `query()`, like this:

``````=arrayformula(
query(
{
A2:A19,
sumif( row(D2:D19), ">=" & row(D2:D19), D2:D19 )
},
"select Col1 where Col2 >= " & Q42 &
"order by Col1 desc limit 1",
0
)
)
``````

If your File > Settings > Locale is such that the spreadsheet uses commas as decimal separators, you will have to use semicolons and backslashes, like this:

``````=arrayformula(
query(
{
A2:A19
\
sumif( row(D2:D19); ">=" & row(D2:D19); D2:D19 )
};
"select Col1 where Col2 >= " & Q42 &
"order by Col1 desc limit 1";
0
)
)
``````
• Thanks so much for your answer! I tried implementing your formula into my sheet, but unfortunately ran into an error: "QUERY PARSE ERROR ENCOUNTERED "<ID> Col2"" at line 1, column 19. Was expecting one of: "("... "("... Not sure what it is expecting though. The brackets look fine. Mar 17, 2022 at 13:30
• Make sure cell `Q42` contains a value like `3`. Consider sharing a publicly editable sample spreadsheet with realistic-looking data. Mar 17, 2022 at 14:08
• Thanks again. I should have shared the sheet earlier. Nevertheless, here is a link: link Mar 17, 2022 at 14:21