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.

 

[steve the fish]

This will do 100 or so columns:

=SUMPRODUCT(--(MOD(COLUMN($C$2:$CZ$2),2)=1),$C$2:$CZ$2)

 

[Aladin Akyurek]

Robustified...

=SUMPRODUCT(--(MOD(COLUMN($C$2:$K$2)-COLUMN($C$2),2)=0),$C$2:$K$2)

 

[caststone]

Thank you very much. Both these formulas are a neat way to sum every other column but as soon as I insert two new columns after column A the formula is 'transposed' (if that is the right word) -

e.g. =SUMPRODUCT(--(MOD(COLUMN($E$2:$M$2)-COLUMN($E$2),2)=0),$E$2:$M$2)

I want it to continue summing C through K, not move to E through M.

Does that make sense?

 

[Jonmo1]

Simplified

=SUMIF($C$1:$CZ$1,"Days",$C2:$CZ2)

 

[caststone]

Again, cracking way to sum the columns until I insert new columns after column A and

=SUMIF($C$1:$CZ$1,"Days",$C2:$CZ2)

Becomes -

=SUMIF($E$1:$DB$1,"Days",$E2:$DB2)

 

[steve the fish]

This wont change but will slow your book down.

INDIRECT("$C$1:$CZ$1")

 

[Jonmo1]

Change it to

=SUMIF($A$1:$CZ$1,"Days",$A2:$CZ2)

 

[caststone]

Thank you Jonmo1, that worked a treat!

Just out of interest Steve - I have never seen 'Indirect' before. What does this do?

 

[Jonmo1]

Thank you Jonmo1, that worked a treat!

You're welcome...

Please note, according to your original post, you're actually putting the formula in cell A2..
The last formula I posted works fine, so long as A1 does NOT = "Days"
If you put "Days" in A1, then it becomes a circular reference error..