SUMIFS formula
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
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 5U = “Duration” in yearsC = Unique numerical digit range assigned to that assetF = Unique numerical digit assigned to that assetL = 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 Read More