Referencing cell one above the current row in a structured table

[SanjayGMusafir]

Hi Experts,
Got stuck up with a situation where I want to tell excel to add values in a Column from Header to the row one above current row.

Unable to figure out how to reference a cell above in a structured table.

The formula I'm currently using is -

SUMIFS((Banks[[#Headers],[Less]]:[@Less]),(Banks[[#Headers],[Acc]]:[@Acc]),[@Acc],(Banks[[#Headers],[Dt]]:[@Dt]),"<"&[@Dt])

The above works fine.

But want to change the red part below from the above formula to get the desired result

(Banks[[#Headers],[Less]]:[@Less])

Please help.

Thanks a lot

 

[SanjayGMusafir]

Now that I understand your formula a little better, you can do it with offset but it wouldn't be nearly as efficient as Peter's version.

=SUMIFS((Banks[[#Headers],[Less]]:OFFSET([@Less],-1,0)),
                 (Banks[[#Headers],[Acc]]:OFFSET([@Acc],-1,0)),[@Acc],
                 (Banks[[#Headers],[Dt]]:OFFSET([@Dt],-1,0)),"<="&[@Dt])

Alex I tried it just now, It's working properly. I believe the error was due to pasted blank values that I have cleared now. This was the formula, I myself tried initially.

Thanks Alex for your help and patience.

 

[Peter_SSs]

Thanks a lot for your effort & patience.

You're welcome. Thanks for the follow-up.

 

[Alex Blakenburg]

Ditto Thanks for the confirmation.