Sum alternate columns over a large (>100) range
 

I need to sum alternate columns over a very large number of columns. Is
there an easy way to do this? Sum(A1+C1+E1+G1 .... ) seems to bomb out
after about 32 entries and I need more than this.
Any Help,
Thanks,

Try instead, something like:
=3DSUMPRODUCT(--(MOD(COLUMN(A1:Z1),2)=3D1),A1:Z1)

or for all 256 cols A to IV
(i.e. the entire row: A1:IV1)
=3DSUMPRODUCT(--(MOD(COLUMN(1:1),2)=3D1),1:1)

--
Rgds
Max
xl 97
---
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
----
Kanga 85 wrote:
I need to sum alternate columns over a very large number of columns.
Is
there an easy way to do this? Sum(A1+C1+E1+G1 .... ) seems to bomb
out
after about 32 entries and I need more than this.
Any Help,
Thanks,

Thanks Max. A rapid response. Looks useful.

"Max" wrote:

Try instead, something like:
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1),2)=1),A1:Z1)

or for all 256 cols A to IV
(i.e. the entire row: A1:IV1)
=SUMPRODUCT(--(MOD(COLUMN(1:1),2)=1),1:1)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
Kanga 85 wrote:
I need to sum alternate columns over a very large number of columns.
Is
there an easy way to do this? Sum(A1+C1+E1+G1 .... ) seems to bomb
out
after about 32 entries and I need more than this.
Any Help,
Thanks,

Kanga 85 wrote:
Thanks Max.
A rapid response.
Looks useful.

You're welcome, Kanga !

--
Rgds
Max
xl 97
---
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
----

"Max" wrote:

Kanga 85 wrote:
Thanks Max.
A rapid response.
Looks useful.

You're welcome, Kanga !


This formula seems to work well for positive numbers, but does not seem to
work if the range includes some negatuve numbers.

Any Ideas?


--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

You want to ignore numbers less than or equal to zero, correct. Here's a
minor modification to Max's first formula that should do the trick:

=SUMPRODUCT(--(MOD(COLUMN(A1:Z1),2)=1),--(A1:Z10),A1:Z1)

See for more info one how this formula works:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

tj

"Kanga 85" wrote:



"Max" wrote:

Kanga 85 wrote:
Thanks Max.
A rapid response.
Looks useful.

You're welcome, Kanga !


This formula seems to work well for positive numbers, but does not seem to
work if the range includes some negatuve numbers.

Any Ideas?


--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Just a couple of thoughts.

You didn't need =sum() in your formula.

=A1+C1+E1+G1+...
would have worked ok.
or
=sum(sum(a1,c1,e1,...up to 30 parms),sum(up to 30 parms),sum(up to 30 parms))

=Sum() has the limitation of only accepting 30 parameters. But you can break it
up into pieces.

Both of these formulas are much uglier than the ones suggested by Max.

But they do have the added benefit that if you insert a column, then the
formulas adjust nicely.

The formulas in Max's suggestions will not calculate what you want if you insert
a single column.

I set up a workbook using formulas similar to Max's and when the user inserted
description columns, the calculation wasn't valid any more.

I like to to insert a helper row and just put an indicator in the columns that
should be summed.

For instance, put # in row 1 for every column that should be added.

Then use a formula like:
=SUMIF($A$1:$L$1,"#",A2:L2)
or even
=SUMIF($1:$1,"#",2:2)
for the whole row



Kanga 85 wrote:

I need to sum alternate columns over a very large number of columns. Is
there an easy way to do this? Sum(A1+C1+E1+G1 .... ) seems to bomb out
after about 32 entries and I need more than this.
Any Help,
Thanks,

--

Dave Peterson

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to you workbook

=SUM(ArrayAlternates(A1:BZ1,,FALSE))

Alan Beban

Kanga 85 wrote:

"Max" wrote:


Kanga 85 wrote:

Thanks Max.
A rapid response.
Looks useful.

You're welcome, Kanga !



This formula seems to work well for positive numbers, but does not seem to
work if the range includes some negatuve numbers.

Any Ideas?



--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

FWIW,

=SUM((A1,A2, ... ,A100))
works!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dave Peterson" wrote in message
...
Just a couple of thoughts.

You didn't need =sum() in your formula.

=A1+C1+E1+G1+...
would have worked ok.
or
=sum(sum(a1,c1,e1,...up to 30 parms),sum(up to 30 parms),sum(up to 30
parms))

=Sum() has the limitation of only accepting 30 parameters. But you can
break it
up into pieces.

Both of these formulas are much uglier than the ones suggested by Max.

But they do have the added benefit that if you insert a column, then the
formulas adjust nicely.

The formulas in Max's suggestions will not calculate what you want if you
insert
a single column.

I set up a workbook using formulas similar to Max's and when the user
inserted
description columns, the calculation wasn't valid any more.

I like to to insert a helper row and just put an indicator in the columns
that
should be summed.

For instance, put # in row 1 for every column that should be added.

Then use a formula like:
=SUMIF($A$1:$L$1,"#",A2:L2)
or even
=SUMIF($1:$1,"#",2:2)
for the whole row



Kanga 85 wrote:

I need to sum alternate columns over a very large number of columns.
Is
there an easy way to do this? Sum(A1+C1+E1+G1 .... ) seems to bomb out
after about 32 entries and I need more than this.
Any Help,
Thanks,

--

Dave Peterson

"Kanga 85" wrote
....
This formula seems to work well for positive numbers,
but does not seem to work if the range includes
some negative numbers.

Puzzled by the above ..

The suggested formula
(for say, a smaller spread of 6 cols: A to F, to illustrate)

=SUMPRODUCT(--(MOD(COLUMN(A1:F1),2)=1),A1:F1)

would simply reduce to
=SUMPRODUCT({1,0,1,0,1,0},A1:F1)

which would then evaluate as:
= 1xA1 + 0xB1 + 1xC1 + 0xD1 +1xE1 + 0xF1
= A1 + C1 + E1

and should return the final outcome correctly
irrespective of whether A1, C1, E1 contains
negative or positive numbers.

Text will be ignored.

If the summation seems not to add up correctly,
then maybe some "numbers" in the target range are
not really numbers, but text ??
(e.g.: '-9, '-3, '9 < entered with preceding apostrophes
in cells will be ignored in the SUMPRODUCT)

Try this experiment on a copy of your sheet:

Suppose your target range to add all the alternate cells
is in A1:Z1

Put in A2: =A1+0, copy across to Z2
(this will coerce any "text" numbers in A1:Z1 to real numbers)

Sum A2:Z2 up in say, A3:
=SUMPRODUCT(--(MOD(COLUMN(A2:Z2),2)=1),A2:Z2)

Now sum up the original target range in say, A4:
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1),2)=1),A1:Z1)

If A3 < A4, then you've got some text numbers
in the target range ..

Merry Christmas !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

To pre-empt possibility of "text" numbers in the target range
not being included in the summation,
try wrapping double negatives around the range "--(...)",
viz. try instead something like:

=SUMPRODUCT(--(MOD(COLUMN(A1:Z1),2)=1),--(A1:Z1))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Thanks Max,
I've sorted out a few problems and had a great Christmas. Happy New Year
to all respondents.

"Max" wrote:

"Kanga 85" wrote
....
This formula seems to work well for positive numbers,
but does not seem to work if the range includes
some negative numbers.

Puzzled by the above ..

The suggested formula
(for say, a smaller spread of 6 cols: A to F, to illustrate)

=SUMPRODUCT(--(MOD(COLUMN(A1:F1),2)=1),A1:F1)

would simply reduce to
=SUMPRODUCT({1,0,1,0,1,0},A1:F1)

which would then evaluate as:
= 1xA1 + 0xB1 + 1xC1 + 0xD1 +1xE1 + 0xF1
= A1 + C1 + E1

and should return the final outcome correctly
irrespective of whether A1, C1, E1 contains
negative or positive numbers.

Text will be ignored.

If the summation seems not to add up correctly,
then maybe some "numbers" in the target range are
not really numbers, but text ??
(e.g.: '-9, '-3, '9 < entered with preceding apostrophes
in cells will be ignored in the SUMPRODUCT)

Try this experiment on a copy of your sheet:

Suppose your target range to add all the alternate cells
is in A1:Z1

Put in A2: =A1+0, copy across to Z2
(this will coerce any "text" numbers in A1:Z1 to real numbers)

Sum A2:Z2 up in say, A3:
=SUMPRODUCT(--(MOD(COLUMN(A2:Z2),2)=1),A2:Z2)

Now sum up the original target range in say, A4:
=SUMPRODUCT(--(MOD(COLUMN(A1:Z1),2)=1),A1:Z1)

If A3 < A4, then you've got some text numbers
in the target range ..

Merry Christmas !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----