Student Pilot Spreadsheet - Retrieving date of last landing

Question

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)

Answer 1

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 
  ) 
)