+ Reply to Thread
Results 1 to 4 of 4

Months to quarters to years

  1. #1
    Registered User
    Join Date
    04-05-2019
    Location
    Helsinki, Finland
    MS-Off Ver
    MS Office 2016 Pro
    Posts
    2

    Months to quarters to years

    Hi,

    I'm a newbie on the forum and tried search to find some help to my simple problem: I have a sheet with months as columns (currently 12*13 years and expect this to grow each year). I have multiple rows with information from other sheets in he same workbook. Now I want to gather this information to another sheet so that it's summarized in quarters (and further in years).

    I tried the following formula, which I found by googling:
    =SUM(OFFSET('Cash Flow M'!$A21;0;3*COLUMNS('Cash Flow M'!$A21:A21)-3;1;3))

    I want to be able to easily copy this downwards and to the right. I did it, but when I go to column N in quarterly sheet, which I expect to calculate cells AL20, AM20, AN20 from the monthly sheet. Unfortunately it doesn't, I can see it from he result. The calculation is:
    =SUM(OFFSET('Cash Flow M'!$A20;0;3*COLUMNS('Cash Flow M'!$A20:M20)-3;1;3))

    As I'm just experimenting with OFFSET for the first time, I can't solve it by myself. Could someone help me? How should I copy this formula so that it works as I expect?

    Thanks in advance!

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Months to quarters to years

    Your offset is 'off' by one column: AK, AL, and AM are the group you would get with the formula as used.

    Just change the offset by 1:

    3*COLUMNS('Cash Flow M'!$A21:A21)-3
    to
    3*COLUMNS('Cash Flow M'!$A21:A21)-2

    And then copy to the right.

    The other method would be to use column headings - say, three rows, where one row is the year, and the next is the month, and the next is the quarter. Then you could use simple SUMIFS functions like

    =SUMIFS(21:21,$1:$1,2019,$3:$3,COLUMN(A1))

    copied to the right for three more cells, to return the four quarters for 2019 for row 21.

    Alternatively, transpose your database to have the dates in a column, and use a pivot table to group the values into quarters.
    Last edited by Bernie Deitrick; 04-05-2019 at 11:03 AM.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    04-05-2019
    Location
    Helsinki, Finland
    MS-Off Ver
    MS Office 2016 Pro
    Posts
    2

    Post Re: Months to quarters to years

    Well, that was easy. Thanks a lot!

    Do you know a good tutorial I could refer to understand how to utilize this more? I have a feeling it would be good to know.

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Months to quarters to years

    What is 'this'? OFFSET, SUMIFS, or Pivot Tables?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculating Rolling Sum based on quarters & years
    By Stathissp in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-04-2018, 10:53 AM
  2. Auto Populate quarters by just typing in the number of years
    By sugar_lips in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-27-2014, 09:31 AM
  3. [SOLVED] Age based on DOB; Accounts for leap years. Years old if >2, Months if <2 years old.
    By sharpmel in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-17-2013, 04:20 PM
  4. Excel 2007 : Quarters and months
    By APPLEBEE in forum Excel General
    Replies: 6
    Last Post: 06-01-2011, 02:02 PM
  5. Replies: 0
    Last Post: 11-28-2007, 06:57 AM
  6. Replies: 15
    Last Post: 12-09-2006, 05:27 PM
  7. Show quarters and years on x axis of a chart
    By Kacee in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-19-2005, 12:06 AM

Tags for this Thread

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