Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Month to Quarter Formula (Excel 2002/SP2)

    I'm trying to reformat some data that I am getting out of a reporting tool. The data current comes into Excel with products in column A and Months in columns B through Z. The month headers have a format of YYYYMM (i.e. 200309), but I really need to be able to view the data by quarters, halfs, and years. I plan to use the columns to the right to consolidate the data into quarters, halfs and years, but as I report on different date ranges in the reporting tool, the number of columns (i.e. months) change.

    I realize that I could manually do a sum(B2:E2) for Q1 and so on, but I'd like to be able to use the month headers with maybe a sumif command so that it would be a single formula across the cells.

    Any help would be appreciated.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Month to Quarter Formula (Excel 2002/SP2)

    If your 200209 is in row 1, you could add 2 rows ( one for "qtr", one for "half")
    In B2 for example enter the formula to cal qtr:
    =INT(VALUE(RIGHT(B1,2)-1)/3)+1
    and in B3 for the half:
    =INT(VALUE(RIGHT(B1,2)-1)/6)+1
    Copy B2:B3 to c2:Z3

    Then use sumifs with those 2 new rows
    Steve

  3. #3
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Month to Quarter Formula (Excel 2002/SP2)

    Steve,

    That works for breaking down the headers into quarters, halfs (and years), but I now need to do a mulptiple "sumif". How can I say "If the years match and the quarters match, sum the cells"?

    Thanks

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Month to Quarter Formula (Excel 2002/SP2)

    You could use a SUMPRODUCT formula (but I would need some details about how you decided to set it up) or even an array formula, but it might just be easier to add the year to the qtr ( and half) formulas:

    =left(B1,4)&"-"INT(VALUE(RIGHT(B1,2)-1)/3)+1
    =left(B1,4)&"-"INT(VALUE(RIGHT(B1,2)-1)/6)+1

    and then do the conventional sumif

    Steve

  5. #5
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Month to Quarter Formula (Excel 2002/SP2)

    Steve,

    Thanks for the help. It works. It's not quite as elegant as I had hoped, but it works.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Month to Quarter Formula (Excel 2002/SP2)

    I used to work in manufacturing: I am happy with something working - elegance is for the academics.

    Steve

Posting Permissions

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