Furnish argument to Sum() function

  • Thread starter Thread starter JMay
  • Start date Start date
J

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,,,,,,,,,,,,,,,,,,,,,,,
 
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,,,,,,,,,,,,,,,,,,,,,,,
 
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
 
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
 
Can you provide alternatives to the below formula,
just so I can get a grasp for excel's syntax logic.
TIA,
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.

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

RagDyer said:
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,,,,,,,,,,,,,,,,,,,,,,,
Click to expand...
Click to expand...
 
Aladin:
Your comments were very helpful.
THANK YOU!!
JMay


Aladin Akyurek said:
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

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