Furnish argument to Sum() function

[JMay]

I need to construct a formula whereby I reference in an argument to a Sum()
function.

For Example: I want in a cell, say A1 to have = Sum(A2:A[the value in
cell B1])

so if B1 = the value is 10
then my A1 will produce the results of =Sum(A2:A10)

How do I accomplish this?

Thanks in Advance,,,,,,,,,,,,,,,,,,,,,,,

 

[RagDyer]

Use Indirect!
Try this:

=SUM(INDIRECT("A2:A"&B1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I need to construct a formula whereby I reference in an argument to a Sum()
function.

For Example: I want in a cell, say A1 to have = Sum(A2:A[the value in
cell B1])

so if B1 = the value is 10
then my A1 will produce the results of =Sum(A2:A10)

How do I accomplish this?

Thanks in Advance,,,,,,,,,,,,,,,,,,,,,,,

 

[Alex]

RagDyer

Doesn't your formula =SUM(INDIRECT("A2:A"&B1)) give you the sum of A2:A10 ( understand that A10 can become any row in Col A_ regardless of what the value of B1 is

Sorry I can't give an alternative to this as I'm not sure what Jmay is trying to achieve

Ale

 

[JMay]

Much appreciated, it works great!!
Thanks,
JMay

 

[RagDyer]

Don't quite follow what you're saying Alex.

An empty B1 gives you an error.

You can enter a formula in B1, and depending on the return of the formula
entered, the Sum range will reflect that return.

If you enter =SUM(A:A) in B1,
B1 *and* the cell containing the Indirect formula will return the *same*
value.
--


Regards,

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

RagDyer,

Doesn't your formula =SUM(INDIRECT("A2:A"&B1)) give you the sum of
A2:A10 ( understand that A10 can become any row in Col A_ regardless of what
the value of B1 is?

Sorry I can't give an alternative to this as I'm not sure what Jmay is
trying to achieve.

Alex

 

[JMay]

Can you provide alternatives to the below formula,
just so I can get a grasp for excel's syntax logic.
TIA,
JMay

 

[Aladin Akyurek]

A non-volatile alternative would be:

=SUM(A2:INDEX(A:A,B1))

RD's use of INDIRECT fixes the computation to column A,
thereby precluding column insertions before the current column A.

Can you provide alternatives to the below formula,
just so I can get a grasp for excel's syntax logic.
TIA,
JMay

Use Indirect!
Try this:

=SUM(INDIRECT("A2:A"&B1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I need to construct a formula whereby I reference in an argument to a Sum()
function.

For Example: I want in a cell, say A1 to have = Sum(A2:A[the value in
cell B1])

so if B1 = the value is 10
then my A1 will produce the results of =Sum(A2:A10)

How do I accomplish this?

Thanks in Advance,,,,,,,,,,,,,,,,,,,,,,,

 

[JMay]

Aladin:
Your comments were very helpful.
THANK YOU!!
JMay

A non-volatile alternative would be:

=SUM(A2:INDEX(A:A,B1))

RD's use of INDIRECT fixes the computation to column A,
thereby precluding column insertions before the current column A.

Can you provide alternatives to the below formula,
just so I can get a grasp for excel's syntax logic.
TIA,
JMay

Use Indirect!
Try this:

=SUM(INDIRECT("A2:A"&B1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I need to construct a formula whereby I reference in an argument to a Sum()
function.

For Example: I want in a cell, say A1 to have = Sum(A2:A[the value in
cell B1])

so if B1 = the value is 10
then my A1 will produce the results of =Sum(A2:A10)

How do I accomplish this?

Thanks in Advance,,,,,,,,,,,,,,,,,,,,,,,