software

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),quot;quot;)
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.
Clivevalaor Wrote:
gt; I need to sum cells A1:A5, C1:C5, E1:E5 etc, i.e. the first top 5 cells
gt; in
gt; every second column. I must sum more than 50 such ranges, so
gt; SUM(A1:A5,
gt; C1:C5, ...) is not an option. Can this be done with formulas?--
Clivey_UK
------------------------------------------------------------------------
Clivey_UK's Profile: www.excelforum.com/member.php...oamp;userid=32569
View this thread: www.excelforum.com/showthread...hreadid=525196If 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

quot;valaorquot; gt; a écrit dans le message de
...
gt; I need to sum cells A1:A5, C1:C5, E1:E5 etc, i.e. the first top 5 cells in
gt; every second column. I must sum more than 50 such ranges, so SUM(A1:A5,
gt; 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 gt; Name gt; define

and enter a name like disjoint.Then just use =SUM(disjoint)
--
Gary''s Studentquot;valaorquot; wrote:

gt; I need to sum cells A1:A5, C1:C5, E1:E5 etc, i.e. the first top 5 cells in
gt; every second column. I must sum more than 50 such ranges, so SUM(A1:A5,
gt; 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(A1L5*(MOD(COLUMN(A1L5),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
quot;sensitivequot; data and use this?

quot;Ardus Petusquot; wrote:

gt; If you have only 5 rows, you can write:
gt; =SUMPRODUCT(--(MOD(COLUMN(A1:K5),2)=1),A1:K1 A2:K2 A3:K3 A4:K4 A 5:K5)
gt;
gt; I could not find any shorter
gt;
gt; HTH
gt; --
gt; AP
gt;
gt; quot;valaorquot; gt; a écrit dans le message de
gt; ...
gt; gt; I need to sum cells A1:A5, C1:C5, E1:E5 etc, i.e. the first top 5 cells in
gt; gt; every second column. I must sum more than 50 such ranges, so SUM(A1:A5,
gt; gt; C1:C5, ...) is not an option. Can this be done with formulas?
gt;
gt;
gt;

Try INDIRECT

--
AP

quot;valaorquot; gt; a écrit dans le message de
...
gt; Thanks for the tip. From your post I got this idea for shortening the
formula:
gt;
gt; =SUMPRODUCT(A1L5*(MOD(COLUMN(A1L5),2)=1))
gt;
gt; It works. Thanks again. I now have a next question: If the ranges are not
gt; evenly spaced, they are still in rows 1-5, but in various columns, not
every
gt; two. Is there a way to have an extra table with the column names holding
gt; quot;sensitivequot; data and use this?
gt;
gt; quot;Ardus Petusquot; wrote:
gt;
gt; gt; If you have only 5 rows, you can write:
gt; gt; =SUMPRODUCT(--(MOD(COLUMN(A1:K5),2)=1),A1:K1 A2:K2 A3:K3 A4:K4 A 5:K5)
gt; gt;
gt; gt; I could not find any shorter
gt; gt;
gt; gt; HTH
gt; gt; --
gt; gt; AP
gt; gt;
gt; gt; quot;valaorquot; gt; a écrit dans le message de
gt; gt; ...
gt; gt; gt; I need to sum cells A1:A5, C1:C5, E1:E5 etc, i.e. the first top 5
cells in
gt; gt; gt; every second column. I must sum more than 50 such ranges, so
SUM(A1:A5,
gt; gt; gt; C1:C5, ...) is not an option. Can this be done with formulas?
gt; gt;
gt; gt;
gt; gt;
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?

quot;Ardus Petusquot; wrote:

gt; Try INDIRECT
gt;
gt; --
gt; AP