Please help with simple adding

[maneesh2312]

Hi All,

I thought I was quite good at Excel but this simple addition has made me think otherwise.

Basically all I want to do is add a range of cells (say B2:B4) but actually its based on a value in cell (A2). For eg., here I want to add upto 3 rows since the value in A2 is 3. Now this value can change (say 6) and for that reason I dont want to hard code it as B2:B4 (adding only 3 rows) nor do I want to keep changing it (B2:B7) as and when the value changes.

Please help how can I achieve this. I am expecting this to be very simple but still its not hitting my head.

__________A____________________B
1_________No of Months__________Amount
2_________3___________________1000
3_____________________________1000
4_________Total Amount________..1000
5_________=sum(B2:B4)________..1000
6_____________________________1000
7_____________________________1000
8_____________________________1000
9_____________________________1000
10____________________________1000

Thanks
Maneesh

Thread also posted at ExcelFourm

 

[Jonmo1]

Welcome to the board...

You can use Offset for this...

The syntax of Offset is:
OFFSET(Reference,#of Rows up/down, #of Cols left/right, Height, Width)

=SUM(OFFSET(B2,0,0,A2,1))


Hope that helps.

 

[T. Valko]

Hi All,

I thought I was quite good at Excel but this simple addition has made me think otherwise.

Basically all I want to do is add a range of cells (say B2:B4) but actually its based on a value in cell (A2). For eg., here I want to add upto 3 rows since the value in A2 is 3. Now this value can change (say 6) and for that reason I dont want to hard code it as B2:B4 (adding only 3 rows) nor do I want to keep changing it (B2:B7) as and when the value changes.

Please help how can I achieve this. I am expecting this to be very simple but still its not hitting my head.

__________A____________________B
1_________No of Months__________Amount
2_________3___________________1000
3_____________________________1000
4_________Total Amount________..1000
5_________=sum(B2:B4)________..1000
6_____________________________1000
7_____________________________1000
8_____________________________1000
9_____________________________1000
10____________________________1000

Thanks
Maneesh

Thread also posted at ExcelFourm

Try it like this...

=SUM(B2:INDEX(B2:B10,A2))

Note that if A2 is an empty cell the formula will calculate the entire range.

 

[markmzz]

Or try this:

=SUM(INDIRECT("B2:B"&A2+1))

Or

=SUM(INDIRECT("B2:B"&A2+LIN(A1)))

Markmzz

 

[T. Valko]

Or try this:

=SUM(INDIRECT("B2:B"&A2+1))

Markmzz

Something to think about...

If you were to insert a new column A that formula no longer works.

 

[markmzz]

More two solutions, are not the most elegants, but solves the problem of the user (Use R and not L for Row).


<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>No of Months</TD><TD>Amount</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1000</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1000</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Total Amount</TD><TD style="TEXT-ALIGN: right">1000</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">2000</TD><TD style="TEXT-ALIGN: right">1000</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">2000</TD><TD style="TEXT-ALIGN: right">1000</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1000</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1000</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>A5</TH><TD style="TEXT-ALIGN: left">=SUM(INDIRECT(ADDRESS(ROW(B2),COLUMN(B2))&":"&LEFT(ADDRESS(ROW(B2),COLUMN(B2)),2)&"$"&A2+ROW(A1)))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>A6</TH><TD style="TEXT-ALIGN: left">=SUM(INDIRECT("L"&ROW(B2)&"C"&COLUMN(B2)&":"&"L"&A2+ROW(A1)&"C"&COLUMN(B2),FALSE))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Markmzz

 

[maneesh2312]

Thanks a lot Everyone !!!

All the three function Offset, Index and Indirect seems to work perfectly. However "Index" function was the one I was looking for and "Indirect" function looks the most simplest to use.

 

[markmzz]

Thanks a lot Everyone !!!

All the three function Offset, Index and Indirect seems to work perfectly. However "Index" function was the one I was looking for and "Indirect" function looks the most simplest to use.

Maneesh2312,

Thanks for the feedback and what is important is that you solved your problem.

Markmzz

 

[T. Valko]

Thanks a lot Everyone !!!

All the three function Offset, Index and Indirect seems to work perfectly. However "Index" function was the one I was looking for and "Indirect" function looks the most simplest to use.

You're welcome. Thanks for the feedback!