adding ever other column

[gma]

how do i add every other column without doing the forumla as A1+C1+E1 ect
ect? I want to be able to total multiple columns. Thanks

 

[Don Guillett]

Look in the help index for MOD.

 

[Mike H]

hi, Change the range to suit

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

Mike

 

[Glenn]

how do i add every other column without doing the forumla as A1+C1+E1 ect
ect? I want to be able to total multiple columns. Thanks

Array-enter (commit with CTRL+SHIFT+ENTER) for odd columns in row 1:

=SUM(IF(MOD(COLUMN(1:1),2),1:1,""))

Switch the true/false results for even columns.

 

[Dave Peterson]

You've gotten answers how to do this. But I wouldn't use those.

If you rearrange your data (inserting/deleting columns -- adding a single
description column in the middle of the range), then the formulas will give you
an answer, but not what you want/expect.

Instead, I'd suggest you add a helper row of indicator values.

Say you insert a new row 1 (shifting everything down a row). Then put something
in row 1 of the columns that should be summed. If you used the character X, you
could use a formula like:

=sumif($1:$1,"x",2:2)

If you insert more columns that should be added, you have to remember to add the
indicator.

And if you don't like seeing row 1 with those indicators, just hide the row.

 

[Mike H]

Dave,

Instead of

You've gotten answers how to do this. But I wouldn't use those

I'm sure you meant to say "Here's an alternative"

Mike

 

[Dave Peterson]

Nope.

I meant that I wouldn't use them.

I think that those formulas are just waiting to give false results.

If I have a workbook that has formulas like this and I forget to adjust those
formulas after a change--or give the workbook to another user who doesn't
understand what needs to be fixed if they make a change to their copy of the
workbook, then the results of the formulas can lead to disastrous results.

I wouldn't recommend them to anyone when =sumif(), =sumifs(), =sumproduct() are
available.

 

[gma]

Thanks Mike that works. One more question, i now want to add the column next
to it so how do i change the formula to skip and pick up the other columns?

 

[Mike H]

Hi,

I'm afraid I don't understand the follow up question. But if you want to add
another cell to the sum use my formula and append a simple sum formula

=SUMPRODUCT(MOD(COLUMN(A1:M1),2)*A1:M1)+N1
or
=SUMPRODUCT(MOD(COLUMN(A1:M1),2)*A1:M1)+Sum(N1:n5)

or a modification to the technique
=SUMPRODUCT((MOD(COLUMN(A1:M1)-COLUMN(OFFSET(A1:M1,,,1,1)),N)=0)*A1:M1)
Change N to the column in the range to sum e.g. a 4 makes it sum every 4th
column

Mike

 

[gma]

Hi Mike
Yeah some times i dont explain very well. I want cell A1 to capture every
other column (C1, D1,G1 and so forth) and cell B1 to capture D1, F1 H1 and
so forth.
thanks