Sum every other column

[himperson1]

Hello all,

I am looking for a formula to sum the contents of every other column/array. i am currently using
=SUMPRODUCT(--(MOD(COLUMN(A1:E1)-COLUMN(A1)+1,2)=1),A1:E1) which works great for adding every other CELL in row 1, but I am looking to add the cells below those as well

ex: adding every odd column

1	2	3	4	5
1	2	3	4	5
1	2	3	4	5

<tbody>
</tbody>

I am trying to get the sum (3+9+15)=27

 

[Eric W]

Try:

=SUMPRODUCT(MOD(COLUMN(A1:E3),2)*A1:E3)

 

[himperson1]

this worked, thank you.

i have altered it to suit my needs which results in:

=SUMPRODUCT(MOD(COLUMN(A1:E3)-COLUMN(A1)+1,2)*A1:E3)

so that the array can begin in any column

 

[Eric W]

You're welcome. I'm glad you got it to work.

 

[himperson1]

this worked, thank you.

i have altered it to suit my needs which results in:

=SUMPRODUCT(MOD(COLUMN(A1:E3)-COLUMN(A1)+1,2)*A1:E3)

so that the array can begin in any column

For posterity:

=SUMPRODUCT(--(MOD(COLUMN(A1:E1)-COLUMN(A1)+1,X)=1),A1:E1)

using this version, changing X to any n digit will correctly allow summation of every other nth column starting anywhere on the worksheet. previous iteration only works for every other column (X = 2).

 

[Aladin Akyurek]

For posterity:

=SUMPRODUCT(--(MOD(COLUMN(A1:E1)-COLUMN(A1)+1,X)=1),A1:E1)

using this version, changing X to any n digit will correctly allow summation of every other nth column starting anywhere on the worksheet. previous iteration only works for every other column (X = 2).

I introduced this eons ago... what I call the robust approach:

1.

=SUMPRODUCT(--(MOD(COLUMN(A1:E1)-COLUMN(A1),X)=0),A1:E1)

which begins summing with the first cell of the reference.

2.

=SUMPRODUCT(--(MOD(COLUMN(A1:E1)-COLUMN(A1)+1,X)=0),A1:E1)

which begins summing with the Xth cell of the reference.