Last Row of TABLE SUM Issue after expanding range?

[Utradeshow]

Hi All,

I have a TABLE that each row has a sum in the last column. When I add rows to the table it changes my sum formula to include all rows in the table? Is there a way to only sum =SUM(W6:AI6) and have the sum remain in that specific row?

 

[Vogateer]

That seems strange, but have you tried using a fixed table reference, or using a fixed cell reference that looks like this:

=SUM($W6:$AI6)

 

[Utradeshow]

I did, I was actually off on the range. Here is the actual sheet and the result after expanding the table.

TTI Agent Rate Calculator v1.03 - TABLE TEST.xlsm
	AH	AI	AJ
3
4
5	UDF3	FUEL	TOTAL COST
6	$0.00	#VALUE!	#VALUE!
7		#VALUE!	#VALUE!
8		#VALUE!	#VALUE!
9		#VALUE!	#VALUE!
10		#VALUE!	#VALUE!
11		#VALUE!	#VALUE!
12		#VALUE!	#VALUE!
13		#VALUE!	#VALUE!
14		#VALUE!	#VALUE!
15		#VALUE!	#VALUE!
16			#VALUE!
ROLLOUT

Cell Formulas
Range	Formula
AI6:AI15	AI6	=VLOOKUP(I6,AGENTS!A:R,17,FALSE)
AJ6:AJ15	AJ6	=SUM($W6:$AI15)
AJ16	AJ16	=SUBTOTAL(109,[TOTAL COST])

Named Ranges
Name	Refers To	Cells
AGENTS!_FilterDatabase	=AGENTS!$A$1:$R$165	AI6:AI15

 

[Vogateer]

Is this an actual Table that can use column references instead of A1 style references? It looks like it is based on the SUBTOTAL formula. What is the exact name of the first and last column that you're summing for each row?

 

[Utradeshow]

Is this an actual Table that can use column references instead of A1 style references? It looks like it is based on the SUBTOTAL formula. What is the exact name of the first and last column that you're summing for each row?

Here is the TABLE with the columns, I need the total after each row to be only for the specific row. I have a VBA that expands the table by the value of a cell A3 in which case may be "20" which would then add 20 rows to the table. It is at that point the SUM formula expands to the number of rows added.

TTI Agent Rate Calculator v1.03 - TABLE TEST.xlsm
	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
5	Handling IN	26' STRAIGHT / DRIVER	DRIVER (OT)	HELPER	HELPER (OT)	STORAGE	STORAGE OVERSIZE2	Handling OUT	DISPOSAL	UDF	UDF2	UDF3	FUEL	TOTAL COST
6	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	$0.00	$0.00	$0.00	#VALUE!	#VALUE!
ROLLOUT

Cell Formulas
Range	Formula
W6	W6	=VLOOKUP(I6,AGENTS!A:G,7,FALSE)*O6
X6	X6	=VLOOKUP(I6,AGENTS!A:K,11,FALSE)*P6
Y6	Y6	=VLOOKUP(I6,AGENTS!A:L,12,FALSE)*Q6
Z6	Z6	=VLOOKUP(I6,AGENTS!A:M,13,FALSE)*R6*P6
AA6	AA6	=VLOOKUP(I6,AGENTS!A:N,14,FALSE)*S6*Q6
AB6	AB6	=VLOOKUP(I6,AGENTS!A:O,15,FALSE)*T6
AC6	AC6	=VLOOKUP(I6,AGENTS!A:P,16,FALSE)*U6
AD6	AD6	=VLOOKUP(I6,AGENTS!A:I,9,FALSE)*O6
AE6	AE6	=VLOOKUP(I6,AGENTS!A:R,18,FALSE)*V6
AI6	AI6	=VLOOKUP(I6,AGENTS!A:R,17,FALSE)
AJ6	AJ6	=SUM($W6:$AI6)

Named Ranges
Name	Refers To	Cells
AGENTS!_FilterDatabase	=AGENTS!$A$1:$R$165	AI6, W6:AE6

 

[Utradeshow]

Here it is with values.

TTI Agent Rate Calculator v1.03 - TABLE TEST.xlsm
	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
5	Handling IN	26' STRAIGHT / DRIVER	DRIVER (OT)	HELPER	HELPER (OT)	STORAGE	STORAGE OVERSIZE2	Handling OUT	DISPOSAL	UDF	UDF2	UDF3	FUEL	TOTAL COST
6	$15.00	$420.00	$0.00	$0.00	$0.00	$0.00	$0.00	$15.00	$0.00	$0.00	$0.00	$0.00	$75.00	$525.00
ROLLOUT

Cell Formulas
Range	Formula
W6	W6	=VLOOKUP(I6,AGENTS!A:G,7,FALSE)*O6
X6	X6	=VLOOKUP(I6,AGENTS!A:K,11,FALSE)*P6
Y6	Y6	=VLOOKUP(I6,AGENTS!A:L,12,FALSE)*Q6
Z6	Z6	=VLOOKUP(I6,AGENTS!A:M,13,FALSE)*R6*P6
AA6	AA6	=VLOOKUP(I6,AGENTS!A:N,14,FALSE)*S6*Q6
AB6	AB6	=VLOOKUP(I6,AGENTS!A:O,15,FALSE)*T6
AC6	AC6	=VLOOKUP(I6,AGENTS!A:P,16,FALSE)*U6
AD6	AD6	=VLOOKUP(I6,AGENTS!A:I,9,FALSE)*O6
AE6	AE6	=VLOOKUP(I6,AGENTS!A:R,18,FALSE)*V6
AI6	AI6	=VLOOKUP(I6,AGENTS!A:R,17,FALSE)
AJ6	AJ6	=SUM($W6:$AI6)

Named Ranges
Name	Refers To	Cells
AGENTS!_FilterDatabase	=AGENTS!$A$1:$R$165	AI6, W6:AE6

 

[Vogateer]

See if this works any better (note: the table name should fill in automatically when you hit enter, and it should fill in before [@[Handling IN]]):

=SUM([@[Handling IN]:[FUEL]])

 

[Utradeshow]

See if this works any better (note: the table name should fill in automatically when you hit enter, and it should fill in before [@[Handling IN]]):

=SUM([@[Handling IN]:[FUEL]])

Yes sir! Works Perfect! Thank you so much