Summing by cells. Inserting cells

[JasonTr]

Hello,

I have a question. Each month I do a report showing the 3 month and 12 month sales $ and units. Every month I add to my database two columns worth of data. How can I anchor my sum calculation to Column 'P' 'R' 'T'? When I insert two columns my calculation currently moves with the cells that are moved to the right.

Thanks.

 

[Jerry Sullivan]

Hi Jason,

You can probably use the Index function in a formula to that.

What is a typicical formula that you are currently using that you want to anchor?

 

[JasonTr]

Hi Jerry,

Yes, I am actually doing the basic summing function. I'll try to make more of a model out of what I am going after.



[TABLE="width: 675"]
<!--StartFragment--> <colgroup><col width="75" span="6"> <col width="75"> <col width="75" span="2"> </colgroup><tbody>[TR]
[TD="width: 75"]Model[/TD]
[TD="width: 75"]3 month $[/TD]
[TD="width: 75"]3 month unit[/TD]
[TD="width: 75"]June $[/TD]
[TD="width: 75"]June Unit[/TD]
[TD="width: 75"]May $[/TD]
[TD="width: 75"]May Unit[/TD]
[TD="width: 75"]April $ [/TD]
[TD="width: 75"]April Unit[/TD]
[/TR]
[TR]
[TD]ABS[/TD]
[TD="align: right"]293[/TD]
[TD] 49[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]244[/TD]
[TD="align: right"]44[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]


If I insert 2 columns to the left of June $. I'll then want to keep the 3 Month $ and 3 month Unit formula to continue to calculate May $, June $, and the new July$.

When I insert new columns the sum function keeps June, May, and April in its formula.

Do you have better advice for setting up this worksheet?

Thanks in advance!

Jason

 

[Jerry Sullivan]

Hey Jason from Portland!

With your existing layout and process of inserting columns each month, here's an Index formula that would work.

Excel Workbook
	M	N	O	P	Q	R	S	T	U
1	Model	3 month $	3 month unit	June $	June Unit	May $	May Unit	April $	April Unit
2	ABS	293	49	25	2	24	3	244	44
Sheet

Hard to advise on a better way to setup the worksheet as this is largely a matter of personal preference.

I tend to orient data so the number of columns is fixed and the number of rows grows as data is added.

Whether you change your orientation or not, you might consider making separate rows or columns for $ and Unit data. This would make for simpler data handling than having those two measure interleaved.

 

[Robert Mika]

Yet another way with added July

Excel 2010
	C	D	E	F	G	H	I	J	K	L	M
1	Model	3 month $	3 month unit	July $	July Unit	June $	June Unit	May $	May Unit	April $	April Unit
2	ABS	59	15	10	10	25	2	24	3	244	44
Sheet2

Cell Formulas
Range	Formula
D2	{=SUM(LOOKUP(SMALL(IF(RIGHT(INDIRECT("$F$1:$P$1"),1)="$",COLUMN(INDIRECT("$F$1:$P$1"))),{1,2,3}),COLUMN(INDIRECT("$F$1:$P$1")),INDIRECT("$F$2:$P$2")))}
E2	{=SUM(LOOKUP(SMALL(IF(RIGHT(INDIRECT("$F$1:$P$1"),4)="Unit",COLUMN(INDIRECT("$F$1:$P$1"))),{1,2,3}),COLUMN(INDIRECT("$F$1:$P$1")),INDIRECT("$F$2:$P$2")))}
Press CTRL+SHIFT+ENTER to enter array formulas.