Dynamically setting the sum formula in excel

2
votes

I want to sum two cells values. Like i want sum of A1 and A2 in C1 then the formulla will be =sum(A1,A2). but in this formulla row number fixed (i.e. 1 and 2). but I want that row number should be decided dynamically in excel.

suppose i have integer values in cell range A1 to A100.Now i want sum of any two values beween A1 to A100. I am putting row number in B1 and B2 and writting this formula in C1 

=SUM(A&B1,A&B2)

so in above formulla Column A is fixed and i want to pick row number from other cell.

for example if i enter the 5 in B1 and 10 in B2 the formulla should sum the A5 and A10 values. Similarly i can enter any value between 1- 100 in column B1 and B2.

I want to do it directly in excel not in macro.

2
excelexcel-2003

2 Answers

2
votes

use INDIRECT to enter the B1 and B2 as variables for A, ie

=SUM(INDIRECT("A"&B1&":A"&B2))

to add A5 and A10 (rather than SUM A5:A10

=INDIRECT("A"&B1)+INDIRECT("A"&B2)
0
votes

Kapil,

In order to satisfy your requirement change the ":" to a "," in the Sum line and you should be good.