Referencing a cell by using a formula to get the name of the cell

Question

I'm not sure how to describe this question but an example should make it clear:

"A"&COUNT(A1:A10000)

gives A10000. So why can't I just do:

SUM(A1:"A"&COUNT(A1:A10000))

which should be the same as SUM(A1:A10000)?

Answer 1

Because you combine int and string, the result become string and it's can't act as a cell no.

give in detail

"A"&COUNT(A1:A10000) gives A10000

1. COUNT(A1:10000) = it's count all cell that has value, supposed all cell in column A has value then it will show result as '10,000'

2. you try to combine String 'A' and int '10,000' so it become String 'A10000'

3. the String 'A10000' is not same as int anymore, it's can't use to sum, divide or multiply since it become String.

   • (ex1. "A"&10000 = String "A10000" is not same as CELL A10000 )

   • (ex2. int 5 = number 5, String '5' = word 'FIVE' )

---

SUM(A1:"A"&COUNT(A1:A10000))

4. next, the second row you are trying to use it as a cell row number.

5. since it is String and it's not a cell row number, so it's can't sum it as CELL A1, A10000 for you.

Answer 2

The INDIRECT function is considered a volatile function. You can accomplish the same thing with the non-volatile INDEX function using the MATCH function to find the last number in a column (in case of interim blanks or interspersed text) rather than counting the numbers in a column.

=sum(A1:INDEX(A:A, MATCH(1e99, A:A)))

The results should be similar to the following.

        

Answer 3

USe the indirect function to convert cell address to range reference:

=sum(indirect("A1:A"&COUNT(A1:A10000))) 

Answer 4

Sure you can:

=SUM(INDIRECT("A1:A"&COUNT(A1:A10000)))