Formula to select last number '1' entered and count '0' that follow

Hi everyone,
Fingers crossed and hoping you can help me out here.

I have a Safety Record spreadsheet with departments (columns) and Date/Day (Rows)

We record against each day a ‘0’ if there’s been no incidences, if there has we record a ‘1’

I need a formula that will look at all the data in just one column, pick out the last ‘1’ entered and then count the ‘0’ afterwards.

This is required so each department can print out their respective Safety Record every morning and display on their KPI boards..

I’ve attached our current spreadsheet and as you’ll see the formulas used are not right, each time there’s an incidence the department rep has to change criteria of the formula.

I’ve highlighted in yellow just one column and the corresponding safety record with current formula.

Thank you all for your time, much appreciated in advance.

Mark

You're a star Glenn, thank you so very much. I struggled to get my head around a formula but what you've shown is teaching me lots, very much appreciated, thank you, oh and Merry Christmas

Hi Glenn, sorry to trouble you, I've been looking at and trying to change the formula to take in all the columns (D to J) and rows so to have a plantwide figure.
I changed the likes of $D:$D to $D:$J, and $D$3:$D$400 to $D$3:$J$400, in my novice mind this looks like it would work all bar the last part of the formula, the $D$400,0 as I gather this is still just looking at column D.
Am I looking along the right lines or am I totally wrong?

=COUNTIF(INDEX($D:$J,IFERROR(AGGREGATE(14,6,ROW($D$3:$J$400)/($D$3:$J$400=1),1),3)):$D$400,0)

Thanks again for your support.
Mark

Yes it would, so would that then be a formula looking at all 7 numbers and selecting the smallest?

Simplicity is the best, looks like I was overthinking the solution, thinking it was harder than it was.
Thank you again Glenn.