Conditional Range w/Sum
 

Ok..I've been trying to bang out a formula that will essentially do a sum on
a variable range within a certain column--Column A. The range will be
determined by a value in another column--Column B. Example: If the value in
cell B1 is between 1-2, the formula sums cells A1 and A2 and returns the
value to, say, cell C2. Any advice on how to set up this kind of variable
range (for lack of a better term) within a formula would be greatly
appreciated.

One way:

C2 =IF(AND(B11,B1<2),SUM(A1:A2),"")

In article ,
"Vlad" wrote:

Ok..I've been trying to bang out a formula that will essentially do a sum on
a variable range within a certain column--Column A. The range will be
determined by a value in another column--Column B. Example: If the value in
cell B1 is between 1-2, the formula sums cells A1 and A2 and returns the
value to, say, cell C2. Any advice on how to set up this kind of variable
range (for lack of a better term) within a formula would be greatly
appreciated.

Look at the OFFSET function

Something like

=SUM(OFFSET(A1,0,0,ROUNDUP(B1)))

Maybe

PC

"Vlad" wrote in message
...
Ok..I've been trying to bang out a formula that will essentially do a sum
on
a variable range within a certain column--Column A. The range will be
determined by a value in another column--Column B. Example: If the value
in
cell B1 is between 1-2, the formula sums cells A1 and A2 and returns the
value to, say, cell C2. Any advice on how to set up this kind of variable
range (for lack of a better term) within a formula would be greatly
appreciated.

In C2, you could use the formula:

=SUM(INDIRECT("A1:A" & B1))

So if B1 were 3, you would get the sum from A1:A3.

HTH,
Bernie
MS Excel MVP


"Vlad" wrote in message
...
Ok..I've been trying to bang out a formula that will essentially do a sum
on
a variable range within a certain column--Column A. The range will be
determined by a value in another column--Column B. Example: If the value
in
cell B1 is between 1-2, the formula sums cells A1 and A2 and returns the
value to, say, cell C2. Any advice on how to set up this kind of variable
range (for lack of a better term) within a formula would be greatly
appreciated.

Another way:

=SUM(A1:INDEX(A:A,B1))
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
In C2, you could use the formula:

=SUM(INDIRECT("A1:A" & B1))

So if B1 were 3, you would get the sum from A1:A3.

HTH,
Bernie
MS Excel MVP


"Vlad" wrote in message
...
Ok..I've been trying to bang out a formula that will essentially do a
sum
on
a variable range within a certain column--Column A. The range will be
determined by a value in another column--Column B. Example: If the value
in
cell B1 is between 1-2, the formula sums cells A1 and A2 and returns the
value to, say, cell C2. Any advice on how to set up this kind of
variable
range (for lack of a better term) within a formula would be greatly
appreciated.