Excel Sum with Relative Positions

Question

How can I sum data from B2 to C2?

B2 = A10 or a total of 10 (preferred)
C2 = A25 or only 25 (preferred)

Usually you just use SUM (A10: A25), but the values ​​in B2 and C2 are not fixed, they change based on the input. I can use MATCH to search for numbers, but how can I say SUM to use these numbers? The values ​​for the sum are always in the same column.

Answer 1

You can use the INDIRECT function for this, for example

=SUM(INDIRECT(B2):INDIRECT(C2)) 

if you can live by entering the entire cell name (A10, A25).

Or just have numbers in B2 and C2, you can use

=SUM(INDIRECT(ADDRESS(B2;1)):INDIRECT(ADDRESS(C2;1)))

(Hope I got the columns and rows in the correct order!)

Answer 2

?

, ?

B1 = "A10"
C1 = "A25"
D1 = =SUM(INDIRECT(B1):INDIRECT(C1))

Answer 3

Use the function INDIRECT(). You pass it a string, which is a range that allows you to have a dynamic range based on input.