Sum every other column - offset function?

[caststone]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1		week no	days	week no	days	week no	days	week no	days	week no	days
2	0
Sheet1

In the simplified table above I am looking to sum all the values in the blue cells(every other column) and show the resulting value in the red cell.

It would seem as simple as =sum(C2+E2+G2+I2+K2)

However the problem I have is that each week I insert a new pair of columns after column A. This means that the formula above allows for the column insertion and changes to =SUM(E2+G2+I2+K2+M2).

I have tried using absolute column references =sum($C2+$E2+$G2+$I2+$K2) but this has no effect.

I have looked at the offset function which seems at least to address the issue of the formula changing as columns are inserted but I cannot understand how to make this work for my particular example.

Any help gratefully received.

 

[caststone]

Good point - I will add a comment to my spreadsheet to that effect so I don't make that mistake. Thank you again.

 

[Jonmo1]

perhaps something like this

=IF($A$1="Days","Remove Days from A1",SUMIF($A$1:$CZ$1,"Days",$A2:$CZ2))

 

[steve the fish]

Indirect requires a text string that refers to a range of cells or a single cell. As it's text it is ignored by dragging/ inserting. It returns the cell references referred to in the text string. It is a volatile function so excel recalculates it each and every time any cell is calculated regardless if the calculated cell is in the 'dependency tree' so can make workbooks slow.

 

[caststone]

Hi Steve - word 'Volatile' scares me so will go with Jonmo1' answer but thank you very much for taking the time to explain it.

and

Jonmo1 - that's an easy way to avoid sending the spreadsheet into a spin , thank you very much.