I need to sum cells A1:A5, C1:C5, E1:E5 etc, i.e. the first top 5 cells in
every second column. I must sum more than 50 such ranges, so SUM(A1:A5,
C1:C5, ...) is not an option. Can this be done with formulas?

There is probably a more elegant way of doing this, but if you put this
formula in A6 and copied it across all the rows you'd get the sum of
every other column:
=IF(COLUMN(A1)=ODD(COLUMN(A1)),SUM(A1:A5),"")
Basically it sums the column if the column is an odd number (1, 3, 5
etc). Then sum the total of this row to give the total of every second
row in all the columns.
If I haven't understood the problem, please explain a little further.
Clive


valaor Wrote:
I need to sum cells A1:A5, C1:C5, E1:E5 etc, i.e. the first top 5 cells
in
every second column. I must sum more than 50 such ranges, so
SUM(A1:A5,
C1:C5, ...) is not an option. Can this be done with formulas?


--
Clivey_UK
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=525196

If you have only 5 rows, you can write:
=SUMPRODUCT(--(MOD(COLUMN(A1:K5),2)=1),A1:K1+A2:K2+A3:K3+A4:K4+A 5:K5)

I could not find any shorter

HTH
--
AP

"valaor" a écrit dans le message de
...
I need to sum cells A1:A5, C1:C5, E1:E5 etc, i.e. the first top 5 cells in
every second column. I must sum more than 50 such ranges, so SUM(A1:A5,
C1:C5, ...) is not an option. Can this be done with formulas?

Very easy:

Just select all the cell-sets you want to sum and pull-down:

Insert Name define

and enter a name like disjoint.


Then just use =SUM(disjoint)
--
Gary''s Student


"valaor" wrote:

I need to sum cells A1:A5, C1:C5, E1:E5 etc, i.e. the first top 5 cells in
every second column. I must sum more than 50 such ranges, so SUM(A1:A5,
C1:C5, ...) is not an option. Can this be done with formulas?

Thanks for the tip. From your post I got this idea for shortening the formula:

=SUMPRODUCT(A1:DL5*(MOD(COLUMN(A1:DL5),2)=1))

It works. Thanks again. I now have a next question: If the ranges are not
evenly spaced, they are still in rows 1-5, but in various columns, not every
two. Is there a way to have an extra table with the column names holding
"sensitive" data and use this?

"Ardus Petus" wrote:

If you have only 5 rows, you can write:
=SUMPRODUCT(--(MOD(COLUMN(A1:K5),2)=1),A1:K1+A2:K2+A3:K3+A4:K4+A 5:K5)

I could not find any shorter

HTH
--
AP

"valaor" a écrit dans le message de
...
I need to sum cells A1:A5, C1:C5, E1:E5 etc, i.e. the first top 5 cells in
every second column. I must sum more than 50 such ranges, so SUM(A1:A5,
C1:C5, ...) is not an option. Can this be done with formulas?

Try INDIRECT

--
AP

"valaor" a écrit dans le message de
...
Thanks for the tip. From your post I got this idea for shortening the
formula:

=SUMPRODUCT(A1:DL5*(MOD(COLUMN(A1:DL5),2)=1))

It works. Thanks again. I now have a next question: If the ranges are not
evenly spaced, they are still in rows 1-5, but in various columns, not
every
two. Is there a way to have an extra table with the column names holding
"sensitive" data and use this?

"Ardus Petus" wrote:

If you have only 5 rows, you can write:
=SUMPRODUCT(--(MOD(COLUMN(A1:K5),2)=1),A1:K1+A2:K2+A3:K3+A4:K4+A 5:K5)

I could not find any shorter

HTH
--
AP

"valaor" a écrit dans le message de
...
I need to sum cells A1:A5, C1:C5, E1:E5 etc, i.e. the first top 5
cells in
every second column. I must sum more than 50 such ranges, so
SUM(A1:A5,
C1:C5, ...) is not an option. Can this be done with formulas?

I did, and it seems I have reached a limit. Initially I was enthusiastic
about the prospect. I tried several variants and always got unwanted results
or errors. After several attempts I tried putting the ranges a1:a5, c1:c5 as
text in cells k1:k4. I tried:

=SUM(INDIRECT(INDEX(K1:K4,ROW(1:4))))

I entered it as an array formula. Still, it only recognizes the first range
a1:a5. After some more search I thought of using N

=SUM(N(INDIRECT(INDEX(K1:K4,ROW(1:4)))))

and it got even worse. It only recognizes a1. Is this impossible? Is there
another way of using indirect here?

"Ardus Petus" wrote:

Try INDIRECT

--
AP