summing non contiguous ranges

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 thi
formula in A6 and copied it across all the rows you'd get the sum o
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,
etc). Then sum the total of this row to give the total of every secon
row in all the columns.
If I haven't understood the problem, please explain a little further.
Clive
 
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
 
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)
 
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?
 
Try INDIRECT

--
AP

valaor said:
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?
Click to expand...
 
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?
 
Back
Top