adding ever other column
 

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

adding ever other column
 

Look in the help index for MOD.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gma" wrote in message
...
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

adding ever other column
 

hi, Change the range to suit

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

Mike


"gma" wrote:

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

adding ever other column
 

gma wrote:
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.

adding ever other column
 

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.



gma wrote:

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

--

Dave Peterson

adding ever other column
 

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" wrote:

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.



gma wrote:

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

--

Dave Peterson
.

adding ever other column
 

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.



Mike H wrote:

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" wrote:

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.



gma wrote:

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

--

Dave Peterson
.


--

Dave Peterson

adding ever other column
 

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" wrote:

hi, Change the range to suit

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

Mike


"gma" wrote:

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

adding ever other column
 

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" wrote:

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" wrote:

hi, Change the range to suit

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

Mike


"gma" wrote:

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

adding ever other column
 

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

"Mike H" wrote:

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" wrote:

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" wrote:

hi, Change the range to suit

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

Mike


"gma" wrote:

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