Return a range based on a variable
 

Column A contains these five numbers: 9,2,3,5,8
Cell A7 contains the number 4

I need a formula that does the following:
Sum the numbers in column A up to the number of rows in A7.

Formula evaluates to 19 (9+2+3+5)

Thanks,
--
Art

Return a range based on a variable
 

Hi,

Try this

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

Mike

"Art" wrote:

Column A contains these five numbers: 9,2,3,5,8
Cell A7 contains the number 4

I need a formula that does the following:
Sum the numbers in column A up to the number of rows in A7.

Formula evaluates to 19 (9+2+3+5)

Thanks,
--
Art

Return a range based on a variable
 

Mike, works perfectly.
Would you (or anyone smarter than me) explain how the formula works?
--
Art


"Mike H" wrote:

Hi,

Try this

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

Mike

"Art" wrote:

Column A contains these five numbers: 9,2,3,5,8
Cell A7 contains the number 4

I need a formula that does the following:
Sum the numbers in column A up to the number of rows in A7.

Formula evaluates to 19 (9+2+3+5)

Thanks,
--
Art

Return a range based on a variable
 

Art,

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

INDIRECT treats the bit in quotes as text and then concatenates the value it
finds in A7 to build a valid Excel formula so say A7 contains the number 4
the formula evaluates as

=sum(a1:a4)

what you can't do is this

=sum(a1:a & a7)
you have to use indirect.

Mike

"Art" wrote:

Mike, works perfectly.
Would you (or anyone smarter than me) explain how the formula works?
--
Art


"Mike H" wrote:

Hi,

Try this

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

Mike

"Art" wrote:

Column A contains these five numbers: 9,2,3,5,8
Cell A7 contains the number 4

I need a formula that does the following:
Sum the numbers in column A up to the number of rows in A7.

Formula evaluates to 19 (9+2+3+5)

Thanks,
--
Art

Return a range based on a variable
 

=SUM(A1:INDEX(A1:A5,A7))


"Art" wrote:

Column A contains these five numbers: 9,2,3,5,8
Cell A7 contains the number 4

I need a formula that does the following:
Sum the numbers in column A up to the number of rows in A7.

Formula evaluates to 19 (9+2+3+5)

Thanks,
--
Art