days since last accident

[Gonzo45]

Can someone help me with a formula which provides the days since last accident with a fatality.

Above in Columns B-I I have data for the given event, what I need is for the filled in sections to have that specific information in order for the days since last Traffic Fatality to appear and this information is subject to change if a newer entry is given based on the date in column E (and of course all of the additional data shown is the same as that above in the example)

I hope this is clear enough and thank you in advance.

Gonzo45

[#2]

@Gonzo45 

=G2-LARGE(IF(H3:H22=1,E3:E22),1)

Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

[#3]

@OliverScheurich 

   My apologies for taking so long to reply, I tried this and it seems I failed to mention another piece to this request. The chart I am using is on a separate sheet from the dates, not to mention there are numerous sheets (4) where the different dates I have are maintained, which I may be able to reduce onto one sheet in another location to use easier, but that may take a little work on my part later. Also the dates are for specific types of accidents (PMV-2, PMV-4, ACV, AMV, NTV) vehicles. These are acronyms for vehicles so don't let it confuse you because they don't seem to make sense otherwise.

   If possible I would want excel to look at a date on each of the 4 sheets and the type vehile acronyms and determine if they meet having a fatality then use the most recent date to make the last traffic fatality "days since last" event and input the number in the box automatically to take out one of us here making errors or forgetting to update that bit of information. Sorry to make it more difficult, and I thank you for any assistance you can give.

Gonzo45

[#4 · Accepted solution]

@Gonzo45 

=$G$2-LARGE(IF((Table1[Fatality]=1)*(Table1[Accident type]=H$1),Table1[Accident date]),1)

You can try this formula which takes the types of accidents into account. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. To simplify the solution i'd recommend to combine all four sheets into one. In this example the data is in a dynamic table and therefore all added records are included in the calculation automatically.

[#5]

Thank you all for your responses, you have helped me tremendously. I am trying to take some courses through Udemy online in order to learn excel a little better (actually a lot), time will tell.