I was try to copy date from row 25 to 26 , seem formula is ok. (I can not enter directly due to my localc may different)
And this is another method. please try if you can add a helper column.
helper column K
K2 ( copy until the end of data )
=AGGREGATE(14,6,ROW(A$1:A2)/(1/(A$1:A2<>"")),1)-ROW()
I2
=IF(AND(B2="DE",H2="Completed"),SUMPRODUCT(
(OFFSET(E2,$K2,,ABS($K2)+1)+OFFSET(F2,$K2,,ABS($K2)+1))-
(OFFSET(C2,$K2,,ABS($K2)+1)+OFFSET(D2,$K2,,ABS($K2)+1)),
--(OFFSET(B2,$K2,,ABS($K2)+1)="DE")),0)
** I can not bring formula in K2 into I2 directly , it's got error and I don't know why it is. T_T
Regards.
Bookmarks