SUMIFS formula

[Jmarch7500]

Hi, I'm trying to write a SUMIFS formula that will sum a total duration based off of a condition rating but stop once it hits a duration value of zero. 

For example please see my current formula: 

=IF($AQ2=5,SUMIFS('2.0_Durations'!$U$2:$U$100000,'2.0_Durations'!$C$2:$C$100000,$F2,'2.0_Durations'!$L$2:$L$100000,"=5"),"-")

AQ = "Condition Rating" so I only want it to sum duration based on condition rating equal to 5

U = "Duration" in years

C = Unique numerical digit range assigned to that asset

F = Unique numerical digit assigned to that asset

L = Condition rating range

My issue is that let's say there are 23 rows of values for this specific asset, the first 19 are in condition rating 5, the next two in condition rating 6, and the last two back to condition rating 5 .  My current formula is summing all durations for that asset that are in condition rating 5 and returning a total duration of 29.2 years.  I only want the formula to sum column 'U' up to row 20 the most recent durations of the asset when it was consecutively in condition rating 5 and/or until it first changes from a 5 to a 6 condition rating (please see screenshot below of data).

Thanks!
Justin 

[#2]

@Jmarch7500 -Please check if this works. Attached file has conventional excel formals if you are using older version of Excel

=SMALL(IF(L5:L27=0,SCAN(0,L5:L27,LAMBDA(X,Y,X+Y))),1)

[#3]

@Jmarch7500 

As variant and for Excel 365

=SUM( TAKE( $L$5:$L$10000,  XMATCH(TRUE, $K$5:$K$10000<>5)-1 ) )

[#4]

@Jmarch7500 

For former Excel versions that could be

=SUM( $L$5:INDEX($L:$L, AGGREGATE(15,6, ROW($K$5:$K$10000)/($K$5:$K$10000<>5),1)-1 ) )

[#5]

@Jmarch7500 

=SUM(IF($I$5:INDEX($I:$I, AGGREGATE(15,6, ROW($K$5:$K$100)/($K$5:$K$100<>5),1)-1 ) =20360, $L$5:INDEX($L:$L, AGGREGATE(15,6, ROW($K$5:$K$100)/($K$5:$K$100<>5),1)-1 ) ))

Or like this with @SergeiBaklan  formula when taking into account the BRKEY values in column C in your file or the column I in the attached sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.

[#6]

Thank you! This did work for that particular BRKEY but I was unable to get it to run for all of them in the list, excel ended up crashing probably due to size of data set.