Get the Max value based on Criteria, then repeat once the criteria is met

Question

I have 4 columns A , B , C , D

I would like to get in column D the maximum miles from column B.

I would like the count to start from column A at First Line. The count should stop at the next First Line then start counting the max again until the next instance of First Line shows up on column A.

Those are my expected results

I have tried this, but this returns the max from the whole column and i don't know how to make it recount once First Line is met.

=IF(I3="Multi-Drop",MAX(H:H),"")

Answer 1

You can use the following to get the max per section, provided you add an additional First Line at the very end, so the function knows where to stop:

=MAX(INDEX(B$2:B2,MATCH("zzz",A$2:A2)):INDEX(B2:B$28,MATCH("First Line",A3:A$28,0)))

Ok, so this is not exactly what you have asked for, and to do that, the formula will have to become longer... The below removes some duplicates, but we still have duplicate 120 because that's the max:

=IF(MAX(INDEX(B$2:B2,MATCH("zzz",A$2:A2)):INDEX(B2:B$28,MATCH("First Line",A3:A$28,0)))=B2,MAX(INDEX(B$2:B2,MATCH("zzz",A$2:A2)):INDEX(B2:B$28,MATCH("First Line",A3:A$28,0))),"")

And the below removes the duplicates of the max as well:

=IF(AND(COUNTIF(INDEX(B$2:B2,MATCH("zzz",A$2:A2)):B2,B2)=1,MAX(INDEX(B$2:B2,MATCH("zzz",A$2:A2)):INDEX(B2:B$28,MATCH("First Line",A3:A$28,0)))=B2),MAX(INDEX(B$2:B2,MATCH("zzz",A$2:A2)):INDEX(B2:B$28,MATCH("First Line",A3:A$28,0))),"")