Add every other column

[Kamarlon]

I have a spreadsheet with actual and budget figures for various departments. Values are from Column C to V.


So this is what I want to do:

Add all the actual figures in columns C,E,G,I,K,M,O,Q,S,U

Add all the budget figures in columns D,F,H,J,L,N,P,R,T,V


What is the formula to add every other even or odd column.

 

[vaskov17]

for odd columns:

=SUM(IF(MOD(COLUMN(A1:D1),2)=1,A1:D1))


for even columns

=SUM(IF(MOD(COLUMN(A1:D1),2)=0,A1:D1))


entered as an array formula CTRL+SHIFT+ENTER

 

[Mike86]

My first suggestion would be to put the data into two columns, one for actual the other for budget. Failing that, here goes:


The C,E,G,...,U range:


=SUM(IF(MOD(COLUMN(C1:V1),2)=1,C1:V1,0))


The D,F,H,...,V range:


=SUM(IF(MOD(COLUMN(C1:V1),2)=0,C1:V1,0))


These are array functions, so enter them using Ctrl-Shift-Enter

 

[Luke M]

Similar to above, but non-array version:

Odd:

=SUMPRODUCT(--(MOD(COLUMN(C1:V1),2)=1),C1:V1)

Even:

=SUMPRODUCT(--(MOD(COLUMN(C1:V1),2)=0),C1:V1)

 

[Kamarlon]

Thanks. It works.