+ Reply to Thread
Results 1 to 7 of 7

Sum a range of columns ?

  1. #1
    Registered User
    Join Date
    02-27-2006
    Location
    Oxford England
    MS-Off Ver
    2007
    Posts
    54

    Sum a range of columns ?

    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

  2. #2
    Miguel Zapico
    Guest

    RE: Sum a range of columns ?

    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" wrote:

    >
    > 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
    >
    >
    > --
    > Ainsley
    > ------------------------------------------------------------------------
    > Ainsley's Profile: http://www.excelforum.com/member.php...o&userid=31960
    > View this thread: http://www.excelforum.com/showthread...hreadid=539848
    >
    >


  3. #3
    Registered User
    Join Date
    02-27-2006
    Location
    Oxford England
    MS-Off Ver
    2007
    Posts
    54
    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 ?

  4. #4
    Miguel Zapico
    Guest

    Re: Sum a range of columns ?

    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" wrote:

    >
    > 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 ?
    >
    >
    > --
    > Ainsley
    > ------------------------------------------------------------------------
    > Ainsley's Profile: http://www.excelforum.com/member.php...o&userid=31960
    > View this thread: http://www.excelforum.com/showthread...hreadid=539848
    >
    >


  5. #5
    Registered User
    Join Date
    02-27-2006
    Location
    Oxford England
    MS-Off Ver
    2007
    Posts
    54
    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 ?

  6. #6
    Ardus Petus
    Guest

    Re: Sum a range of columns ?

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

    HTH
    --
    AP

    "Ainsley" <Ainsley.27j89a_1147164601.1011@excelforum-nospam.com> a écrit
    dans le message de news:
    Ainsley.27j89a_1147164601.1011@excelforum-nospam.com...
    >
    > 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 ?
    >
    >
    > --
    > Ainsley
    > ------------------------------------------------------------------------
    > Ainsley's Profile:
    > http://www.excelforum.com/member.php...o&userid=31960
    > View this thread: http://www.excelforum.com/showthread...hreadid=539848
    >




  7. #7
    Registered User
    Join Date
    02-27-2006
    Location
    Oxford England
    MS-Off Ver
    2007
    Posts
    54
    Fantastic, sweet and simple.

    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1