Sum alternate columns over a large (>100) range

K

Kanga 85

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,
 
M

Max

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)
 
K

Kanga 85

Thanks Max. A rapid response. Looks useful.

Max said:
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)
Click to expand...
 
K

Kanga 85

Max said:
You're welcome, Kanga !
Click to expand...

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

Any Ideas?
 
T

tjtjjtjt

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:Z1>0),A1:Z1)

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

tj

Kanga 85 said:
Max said:
You're welcome, Kanga !
Click to expand...

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

Any Ideas?

Click to expand...
 
D

Dave Peterson

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
 
M

Max

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))
 
Top