summing non contiguous ranges

[valaor]

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]

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

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?

[Ardus Petus]

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

I could not find any shorter

HTH
--
AP

"valaor" <[email protected]> a écrit dans le message de
news:[email protected]...
> 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?

[Gary''s Student]

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?

[valaor]

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+A5:K5)
>
> I could not find any shorter
>
> HTH
> --
> AP
>
> "valaor" <[email protected]> a écrit dans le message de
> news:[email protected]...
> > 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?
>
>
>

[Ardus Petus]

Try INDIRECT

--
AP

"valaor" <[email protected]> a écrit dans le message de
news:[email protected]...
> 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+A5:K5)
> >
> > I could not find any shorter
> >
> > HTH
> > --
> > AP
> >
> > "valaor" <[email protected]> a écrit dans le message de
> > news:[email protected]...
> > > 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?
> >
> >
> >

[valaor]

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