SumIf up to a threshold(number) and spit out the corresponding date

%3CLINGO-SUB%20id%3D%22lingo-sub-1898224%22%20slang%3D%22en-US%22%3ESumIf%20up%20to%20a%20threshold(number)%20and%20spit%20out%20the%20corresponding%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1898224%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20been%20looking%20for%20a%20formula%20for%20over%20a%20week%2C%20and%20I%20just%20dont%20think%20I%20am%20googling%20the%20correct%20words%20to%20get%20my%20answer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20situation%3A%20I%20have%20a%20logbook%20in%20excel%20of%20my%20flying%20history.%20It%20has%20a%20multitude%20of%20figures%20on%20it%2C%20but%20this%20issue%20only%20deals%20with%20%22Single%20Engine%20Time%22%2C%20%22Night%20Time%22%20and%20%22Multi%20Engine%20Time%22%20as%20well%20as%20%22Day%20Landings%22%20and%20%22Night%20Landings%22.%20to%20stay%20current%20I%20have%20to%20have%203%20landings%20within%20the%20past%2090%20days%20in%20said%20total%20categories%20which%20would%20be%20%22single%20engine%20-%20Night%22%20landings%20or%20%22Multi%20engine%22%20landings%20or%20%22Multi%20Engine%20Night%22%20landings%20as%20examples.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20already%20have%20this%20completed%2C%20but%20it%20is%20through%20multiple%20columns%20of%20ongoing%20formulas%2C%20and%20the%20most%20annoying%20part%20of%20all%20is%20that%20when%20I%20insert%20a%20row%20to%20record%20a%20new%20flight%20I%20have%20to%20manually%20drop%20down%20the%20formula.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20believe%20what%20I%20can%20do%20is%20through%20SUMIFs%2C%20IFs%2C%20MAXIFS%2C%20MINIFS(to%20spit%20out%20the%20date)%20combine%20them%20to%20get%20this%20done.%20so%20I%20think%20the%20best%20way%20is%20to%20SUMIF%20my%20conditions%20(For%20example%2C%20Single%20Engine%20Night%20Landings)%20So%20if%20Single%20Engine%20Time%20%26gt%3B0%20and%20Night%20Time%20%26gt%3B0%20then%20sum%20Night%20landings.%20this%20would%20give%20me%20a%20total%20number%20of%20single%20engine%20night%20landings%2C%20and%20lets%20say%20that%20number%20is%20100.%20No%20I%20need%20to%20find%20the%20date%20that%20would%20correspond%20to%2097%20because%20I%20need%20to%20know%20when%20the%20date%20I%20was%20current%20is%20which%20I%20need%203%20landings%20to%20be%20current%20ie.%20100-3%3D97.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20ultimately%20it%20would%20be%20a%20date%2011%2F12%2F2020%20is%20when%20I%20was%20current%2C%20and%20am%20current%20until%2011%2F12%2F2020%20%2B%2090%20Days.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIm%20sure%20this%20is%20confusing%20but%20any%20help%20would%20be%20fantastic.%20been%20struggling%20with%20it%20for%20a%20while%2C%20and%20while%20I%20love%20a%20good%20puzzle%20this%20one%20is%20driving%20a%20hole%20in%20my%20head.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3EMH%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1898224%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1898556%22%20slang%3D%22en-US%22%3ERe%3A%20SumIf%20up%20to%20a%20threshold(number)%20and%20spit%20out%20the%20corresponding%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1898556%22%20slang%3D%22en-US%22%3E%3CP%3EMaybe%20this%20will%20clarify.%20I%20am%20trying%20to%20find%20a%20way%20to%20consolidate%20the%20data%20in%20column%20AH%20(A%20running%20total%20of%20Multi%20Engine%20Night%20Landings)%20and%20the%20formula%20in%20AH247%20into%20a%20single%20cell%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1898557%22%20slang%3D%22en-US%22%3ERe%3A%20SumIf%20up%20to%20a%20threshold(number)%20and%20spit%20out%20the%20corresponding%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1898557%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20picture%20was%20wrong%20lol.%20This%20is%20a%20screenshot%20of%20my%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have been looking for a formula for over a week, and I just dont think I am googling the correct words to get my answer.

 

the situation: I have a logbook in excel of my flying history. It has a multitude of figures on it, but this issue only deals with "Single Engine Time", "Night Time" and "Multi Engine Time" as well as "Day Landings" and "Night Landings". to stay current I have to have 3 landings within the past 90 days in said total categories which would be "single engine - Night" landings or "Multi engine" landings or "Multi Engine Night" landings as examples. 

 

I already have this completed, but it is through multiple columns of ongoing formulas, and the most annoying part of all is that when I insert a row to record a new flight I have to manually drop down the formula. 

 

I believe what I can do is through SUMIFs, IFs, MAXIFS, MINIFS(to spit out the date) combine them to get this done. so I think the best way is to SUMIF my conditions (For example, Single Engine Night Landings) So if Single Engine Time >0 and Night Time >0 then sum Night landings. this would give me a total number of single engine night landings, and lets say that number is 100. No I need to find the date that would correspond to 97 because I need to know when the date I was current is which I need 3 landings to be current ie. 100-3=97. 

 

So ultimately it would be a date 11/12/2020 is when I was current, and am current until 11/12/2020 + 90 Days. 

 

Im sure this is confusing but any help would be fantastic. been struggling with it for a while, and while I love a good puzzle this one is driving a hole in my head. 

 

Thanks!

MH

2 Replies
Highlighted

Maybe this will clarify. I am trying to find a way to consolidate the data in column AH (A running total of Multi Engine Night Landings) and the formula in AH247 into a single cell formula.

Highlighted

That picture was wrong lol. This is a screenshot of my sheet.