How to dynamically add up varying # of cells base on a value

F

Felicia

How do I get a formula to decide on the # of cells to add up base on the
value in another cell.

For example, column A to L contains the $saving for each months of the
year from Jan to Dec. And say that savings for the first x months of the
year could be carried over from the previous year; and that x value is
reflected in column M. And I want to know the total $saving that is carried
over from the previous year. So, how do get the formula to add up the first
x number of months and put the value in column O.


Jan Saving Feb Saving Mar Saving Apr Saving May Saving Jun
Saving Jul Saving Aug Saving Sep Saving Oct Saving Nov Saving
Dec Saving # Months carried over Savings Carried Over
100 200 150 160 300 350 400 200 200 300 300 400 4 610
100 100 200 250 150 700 600 700 600 500 550 500 5 800
300 400 300 250 200 800 300 500 600 500 550 500 0 0





Thanks very much.
 
S

Sheeloo

With 5 in B1 the following will sum the range A1:A5
=SUM(INDIRECT("A1:A"&B1))

if you change B1 to 10 it will sum the range A1:A10
 
T

T. Valko

Try this:

=SUM(A1:INDEX(A1:L1,n))

Where n = a number or a reference to a cell that holds the number.

A5 = 6

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

That will sum A1:F1. If A5 is an empty cell the formula will calculate the
entire range.
 
F

Felicia

Hi, Sheeloo:

Thanks for the suggestion. Tried it out and if I understand this formula
correctly, it seems to sums up range of cells in the same column (ie A1:A5)
only. So, what if I needed to sum up range of cells in the same row (ex
A1:L1) ?

T. Valko also responded with the suggestion of using INDEX. Is that the
approach for suming up cross the row or is there still possible option by
using the INDIRECT function ? Just wanted to confirm. And eitherway, great
to learn about this INDIRECT function as well as I'm sure I'll find use for
it later.

Felicia
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculated Item causes undue repitition in pivot table 2
Column Heading on each row in pivot table 1
finding is a value is betwen a range of values 5
Pivot table and value range 2
Sort with IF functions? 5
Create a new workbook for all Unique values and new work sheets tothat new workboks 0
calculating values where it depends on the value of a previous cel 2
Add columns to reach a predetermined value 2

Top