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

 

[Peter_SSs]

I want to tell excel to add values in a Column from Header to the row one above current row.

See if this example is the sort of thing you want.

SanjayGulatiMusafi.xlsm
	I	J
3	Amt	Sum
4	5	0
5	6	5
6	3	11
7	2	14
8	4	16
Sheet1

Cell Formulas
Range	Formula
J4:J8	J4	=SUM(INDEX([Amt],1):[@Amt])-[@Amt]

 

[SanjayGMusafir]

See if this example is the sort of thing you want.

SanjayGulatiMusafi.xlsm
	I	J
3	Amt	Sum
4	5	0
5	6	5
6	3	11
7	2	14
8	4	16
Sheet1

Cell Formulas
Range	Formula
J4:J8	J4	=SUM(INDEX([Amt],1):[@Amt])-[@Amt]

I think I got the idea, let me try what you want to suggest and I Revert back. At times, we complicate things and don't look at the easier side of things.

Thanks again, I just revert back

 

[SanjayGMusafir]

It did the magic -

I used your concept this way, as the blank cell were causing Value Error

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

 

[Alex Blakenburg]

If you are not doing it across thousands of rows and not adverse to using offset then this works:

(Banks[[#Headers],[Less]]:offset([@Less],-1,0))

 

[SanjayGMusafir]

If you are not doing it across thousands of rows and not adverse to using offset then this works:

(Banks[[#Headers],[Less]]:offset([@Less],-1,0))

Alex I started with this only, but it was giving error, as offset formula returns value of the cell

 

[Peter_SSs]

the blank cell were causing Value Error

My guess is that they are not blank but contain a formula returning ""?

This table has your formula in column J and my formula, slightly adjusted to cope with "" cells in column K and both return the same results.

SanjayGulatiMusafi.xlsm
	G	H	I	J	K
17	Less	Acc	Dt	Sanjay	Peter
18	5	a	3	0	0
19	6	b	3	0	0
20	3	a	3	5	5
21	2	b	3	6	6
22	2	a	55	8	8
23	5	a	3	8	8
24	4	a	3	13	13
25	6	a	3	17	17
26		a	3	23	23
27		a	3	23	23
28		a	3	23	23
Sheet1

Cell Formulas
Range	Formula
J18:J28	J18	=SUMIFS((Banks[[#Headers],[Less]]:[@Less]),(Banks[[#Headers],[Acc]]:[@Acc]),[@Acc],(Banks[[#Headers],[Dt]]:[@Dt]),"<="&[@Dt])-IFS([@Less]="",0,TRUE,[@Less])
K18:K28	K18	=SUMIFS(INDEX([Less],1):[@Less],INDEX([Acc],1):[@Acc],[@Acc],INDEX([Dt],1):[@Dt],"<="&[@Dt])-N([@Less])

 

[Alex Blakenburg]

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])

 

[SanjayGMusafir]

My guess is that they are not blank but contain a formula returning ""?

This table has your formula in column J and my formula, slightly adjusted to cope with "" cells in column K and both return the same results.

SanjayGulatiMusafi.xlsm
	G	H	I	J	K
17	Less	Acc	Dt	Sanjay	Peter
18	5	a	3	0	0
19	6	b	3	0	0
20	3	a	3	5	5
21	2	b	3	6	6
22	2	a	55	8	8
23	5	a	3	8	8
24	4	a	3	13	13
25	6	a	3	17	17
26		a	3	23	23
27		a	3	23	23
28		a	3	23	23
Sheet1

Cell Formulas
Range	Formula
J18:J28	J18	=SUMIFS((Banks[[#Headers],[Less]]:[@Less]),(Banks[[#Headers],[Acc]]:[@Acc]),[@Acc],(Banks[[#Headers],[Dt]]:[@Dt]),"<="&[@Dt])-IFS([@Less]="",0,TRUE,[@Less])
K18:K28	K18	=SUMIFS(INDEX([Less],1):[@Less],INDEX([Acc],1):[@Acc],[@Acc],INDEX([Dt],1):[@Dt],"<="&[@Dt])-N([@Less])

Sorry Peter for the late response, I was not well. Just now I tried it. It's shorter than my version and delivers the intended result.

And the cells that were giving error was due to pasted blank values there. Once I cleared the cells, error was gone.

Thanks a lot for your effort & patience.

 

[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 sorry for the late response, I was not well.

I just try and revert on it, though got a much shorter formula from Peter.