Sum a range of columns ?

[Ainsley]

I have a simple budget with columns for each month (month 1 to 12) and a
Year to Date column at the end.

If it is now say month 3, how do I get my YTD column to just add up the
first three columns. I have a cell that tells me what month we are in,
so I can use this in the formula, but I cant think of how to write the
formula that adds up this specified number of columns.

I want somthing that says it is now month 'x', so add up months 1 to
'x'. Each time I put in the month number, I want to see the YTD sum
without changing that formula.

Any help will be appreciated.

Regards, AB

 

[Guest]

You can use indirect for achieving this. Suppose that we have the month data
in column B (B1:B12), and the cell where you want to enter the moving month
is C1. You can enter this formula is C2, for example:
="B1:B" & C1
And then in B13 (or wherever fits)
=SUM(INDIRECT(C2))

Hope this helps,
Miguel.

 

[Ainsley]

Hi, Im not sure I understand.

I have in Column A Jan data, Column B Feb data and so on to Dec in
Column L. The a Column M which is to be the sum of several of these
months, depending upon a number in a seperate cell. This being the
month number we are in.

Jan being month 1, Feb being month 2 and so on to Dec being month 12.

So if it is currently month 5, I want my Year to Date Column to sum
columns Jan to May simply by me inputting a 5 in a cell say above my
YTD column.

What formula do I use to achive this ?

 

[Guest]

Hi,

I have made the assumtion that the data was in rows, not in columns.
The idea behind the formula is craft dinamically a range name (for example
A2:E2) and then sum the data on this range. We use INDIRECT to achieve this.
In this case, if you have the cell where you want to enter the month in P1,
the formula to use in the M column can be:
=SUM(INDIRECT("A"& ROW(A2) & ":" &ADDRESS(ROW(A2),$P$1)))
This is for cell M2, you can copy this formula over the column.

Hope this helps,
Miguel.

 

[Ainsley]

Hi, thanks for that. Ive used it but cant get it to work exactly all
situations.

If my data starts in Column E (for Jan) and ends in Column P (Dec) and
the YTD is in Column T and the month number in Column V I cant seem to
make the formula work. The data all starts on row 11.

I also cant seem to copy it to other rows downward.

Ive typed
=SUM(INDIRECT("E"& ROW(E11) & ":" &ADDRESS(ROW(E11),$V$11)))

But its not working. What am i doing wrong ?

 

[Ardus Petus]

=SUM(OFFSET(E11,,,1,V11))

HTH

 

[Ainsley]

Fantastic, sweet and simple.

Cheers