1

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)

Screenshot Sheets

1

1 Answer 1

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 
  ) 
)
4
  • 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.
    – Klavkjir
    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
    – Klavkjir
    Mar 17, 2022 at 14:21
  • Edited the answer to add a formula localized for Netherlands. See your sample spreadsheet. Mar 18, 2022 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.