+ Reply to Thread
Results 1 to 4 of 4

Excel 2007 : Sum Quarters

  1. #1
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Sum Quarters

    OK, spent all day trying to figure out how to do this and still can't get it to work.

    Column B is a column of Dates, say Jan 2010 to Dec 2060 at monthly intervals

    Column F is the income value for each month.

    Cell A1 allows the user to enter any date

    I need a formula to look at the date in Cell A1 and then sum the total income for the appropriate quarter that this date falls in (assumes standard year quarters of Mar, Jun, Sept and Dec), so for example if the user enters April 4th 2012 in Cell A1, this falls into the second quarter, so my formula needs to total the income from Column F for April, May and June 2012.

    I have no problem identifying the quarter that the date falls in using

    =ROUNDUP(MONTH($A$1)/3,0)

    but I am struggling to get my formula to identify all the months that fall in the quarter and then sum the income for just those months from Column F. Every variation I've tried returns zero.

    I've tried variations on SUMPRODUCT and SUMIFS:

    =SUMPRODUCT(--($B$1:$B$360=YEAR($A$1)),--($B$1:$B$360=ROUNDUP(MONTH($A$1)/3,0)),--($F$1:$F$360))

    YEAR($A$1) identifies the year correctly, ROUNDUP(MONTH($A$1)/3,0), identifies the correct quarter but I realise that this maybe where my formula is not right.

    Can anyone please help as I can't figure this one out.

    Ultimately I am looking to use the formula to calculate income for the current quarter (based on the date entered in A1), the previous quarter and following quarter, which will all be variations on the same formula...

    Many thanks
    Last edited by HangMan; 06-23-2010 at 12:47 PM.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Sum Quarters

    Try

    =SUMPRODUCT(--(YEAR($B$1:$B$360)=YEAR($A$1)),--(MONTH($B$1:$B$360)=ROUNDUP(MONTH($A$1)/3,0)),$F$1:$F$360),

    although if you are always summing 3 amounts you might be able to do that like this, assuming your dates in column B are always 1st of the month

    =SUM(OFFSET($F$1,MATCH(DATE(YEAR($A$1),FLOOR(MONTH($A$1)-1,3)+1,1),$B$1:$B$360,0)-1,0,3))
    Audere est facere

  3. #3
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Sum Quarters

    Hi daddylonglegs

    Have adapted your SUMPRODUCT version because the year in cell A1 (now E1 in the attached sample) needs to consider what happens if the next quarter falls into the following year, but the year in your formula always resolves to the year in E1 and also the total comes to zero. For example, if the date entered in E1 is in December, the resulting income total would need to be for the first 3 months of the following year. I have attached a sample (a stripped down version of my spreadsheet, so the column references have changed). So I now have

    =SUMPRODUCT(--(YEAR($B$2:$B$101)=YEAR(EDATE($E$1,3)))--(ROUNDUP(MONTH($B$2:$B$101)/3,0)=ROUNDUP(MONTH(EDATE($E$1,3))/3,0)),--($C$2:$C$101))

    This is giving me a result, but not the right result. In fact it is totalling all entries for the specified year plus all the entries for the specified month in column C and then adding the amount from the single cell that matches the specified month and year on top. I have evaluated the formula and I can't see any errors at any stage, so still unsure where I am going wrong.

    I tried the offset formula and am currently getting a N/A error. I think the SUMPRODUCT option is close, but just need to figure out the error.

    Let me know if you have any further thoughts or if you can spot the error?

    Many thanks, very grateful for your help
    Attached Files Attached Files
    Last edited by HangMan; 06-24-2010 at 02:12 PM.

  4. #4
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Sum Quarters

    OK, just spotted my error - missing commas, all working now.

    =SUMPRODUCT(--(YEAR($B$2:$B$101)=YEAR(EDATE($E$1,3))),--(ROUNDUP(MONTH($B$2:$B$101)/3,0)=ROUNDUP(MONTH(EDATE($E$1,3))/3,0)),--($C$2:$C$101))

    Many thanks daddylonglegs...
    Last edited by HangMan; 06-25-2010 at 11:32 AM.

+ 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