Multi-Row Tool Usage Enhancement

[knnwndlm]

Hi SME,

I have built a one-year backward/forward calculation using the Multi-Row Formula tool shown in column L in the attached file. The results from the calculation are shown in column F. 

Now, I want to build a two-year backward/forward calculation using an enhanced version of the Multi-Row Formula earlier.  However, my formula got very complicated very quickly due to too many conditions imposed on the calculation. Now, I have four cells of data to consider, so you can imagine how unmanageable my Multi-Row Formula can be.

Any suggestions on a better way to handle this?

Thanks,

kwl

[knnwndlm]

@PangHC - Think I figured it out. The formula I ended up using is:  

IIF([Row-2:YEAR] IN ([YEAR]+1, [YEAR]+2) AND [Row-2:AMT] >= 0, [Row-2:AMT], 0) +
IIF([Row-1:YEAR] IN ([YEAR]+1, [YEAR]+2) AND [Row-1:AMT] >= 0, [Row-1:AMT], 0) +
IIF([Row+1:YEAR] IN ([YEAR]-1, [YEAR]-2) AND [Row+1:AMT] >= 0, [Row+1:AMT], 0) +
IIF([Row+2:YEAR] IN ([YEAR]-1, [YEAR]-2) AND [Row+2:AMT] >= 0, [Row+2:AMT], 0)

[PangHC]

@knnwndlm the formula is correct. but i suggest to use a summary to sum the total first. to remove the duplicate. 
you can add a formula to remove the negative. where it can easy apply to even 100 rows for a year. 

[knnwndlm]

@PangHC - The line items with the same year are not dups. They came about because of the additional fields that I needed.  In any case, I was able to resolve this using a Batch macro.  All good now.  Thank you!