Sum every other Column using Spill

[Finalfight40]

Hi All

I have a table similar to below. I am currently using the formula's in Column J. This works well summing Columns B, S, F & H and also allows me to insert more columns in the middle. However when i want to insert rows, i have to drag the formula down.

I was wondering if there is a way with the new spill feature if i can modify to formula so that i will not have to drag the formula down anymore when inserting new rows. For example in cell L4 but still allowing me to add columns in the middle.

Hope this makes sense.

Booking (6).csv
	B	C	D	E	F	G	H	I	J	K	L
3									Current Formula		Not Like this
4	1	2	3	4	5	6	7	8	16		16
5	2	3	4	5	6	7	8	9	20		20
6	1	0	1	0	1	0	1	0	4		4
Sheet2

Cell Formulas
Range	Formula
L4:L6	L4	=B4:B6+D4:D6+F4:F6+H4:H6
J4:J6	J4	=SUMPRODUCT(--(MOD(COLUMN(B4:I4)-COLUMN(B4), 2)=0), B4:I4)
Dynamic array formulas.

 

[J.Ty.]

I do not understand the purpose of your formulas because those below the first row are accessing cells outside of the data area. Perhaps there was something in your spreadsheet XL2BB selection does not show.

In any case, you can test converting your columns A:K into a table. Mark the whole area and hit Ctrl-T. After that if you add a new data below, the table extends itself automatically, including copying of the formulas.

J.Ty.

 

[Finalfight40]

I had not considered turning this into a table. I was playing around with spilling everything so i guess i got caught up with the idea.

Thanks