+ Reply to Thread
Results 1 to 10 of 10

Excel 2007 : Sum Quarters using SUMIFS rather than SUMPRODUCT

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

    Sum Quarters using SUMIFS rather than SUMPRODUCT

    I have a SUMPRODUCT formula to sum current, previous and next quarters from a long list of sales income over a 25 year period. The formula looks at the date in a specified cell (D2) to determine which quarter the month falls into and in the example below totals the income for the previous quarter (assumes Q1 = Jan, Feb, Mar, Q2 = Apr, May, Jun etc).

    See sample file attached.

    I have Dates in Column A, 31-Jan-2001, 28-Feb-2001, 31-March-2001 and so on for 25 years and sales totals in Column B.

    To calculate the previous quarter my SUMPRODUCT formula is

    Please Login or Register  to view this content.

    This works perfectly...

    However, I need to achieve the same thing using a SUMIFS formula but I can't seem to get this to work, it always resolves to zero!

    Please Login or Register  to view this content.

    Where am I going wrong?
    Attached Files Attached Files
    Last edited by HangMan; 07-11-2010 at 01:14 PM.

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Sum Quarters using SUMIFS rather than SUMPRODUCT

    It won't work because you cannot use an expression for the criteria range in SUMIF/SUMIFS, so you cannot have YEAR($A:$A)

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

    Re: Sum Quarters using SUMIFS rather than SUMPRODUCT

    Hi Bob

    Thanks for your quick reply - is there any way to achieve the same outcome but still using a SUMIFS formula?

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

    Re: Sum Quarters using SUMIFS rather than SUMPRODUCT

    You say you can't use an expression for the criteria range in SUMIF/SUMIFS. I am using a whole load of other formula to calculate different periods for the same data which successfully use an expression for the criteria range, for example this formula calculates the 'Next 12 months' from the date in D2.

    Please Login or Register  to view this content.

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Sum Quarters using SUMIFS rather than SUMPRODUCT

    Why do you need to use SUMIFS, you could probably use SUM and OFFSET like this

    =SUM(OFFSET(B1,MATCH(EOMONTH(D2,MOD(-MONTH(D2),3)-5),A:A,0)-1,0,3))

    or if you really want SUMIFS try

    =SUMIFS(B:B,A:A,">="&EOMONTH(D2,MOD(-MONTH(D2),3)-5),A:A,"<="&EOMONTH(D2,MOD(-MONTH(D2),3)-3))
    Audere est facere

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

    Re: Sum Quarters using SUMIFS rather than SUMPRODUCT

    daddylonglegs

    That works perfectly, many thanks (I just need to understand it now).

    As SUM also works then that is also good, I don't know which of your two formula has the greater overhead. My main issue was not being able to use SUMPRODUCT because as far as I can tell SUMPRODUCT causes issues when using $A:$A if your column of data varies in length and includes #NUM!. SUMPRODUCT includes the #NUM! in its calculation whereas SUMIFS ignores them because it is matching criteria from two columns.

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

    Re: Sum Quarters using SUMIFS rather than SUMPRODUCT

    I'm now trying to figure how to apply the same logic (using either SUMIF or SUMIFS) to total all dates that fall in Qtr1, Qtr2 etc for the whole list.

    So this time the total isn't based on the date in D2 but needs to sum all the totals for Jan, Feb and Mar (for Qtr 1) over the whole 25 years?

    Really struggling with how to define the appropriate months for the dates when not basing it on a fixed date?
    Last edited by HangMan; 07-11-2010 at 04:21 PM.

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Sum Quarters using SUMIFS rather than SUMPRODUCT

    Bob's point was that you can't use a function (like MONTH or YEAR) to modify a range in SUMIFS (although you can use functions in the criteria).

    That will prevent you from using SUMIF(S) for summing all quarter 1 values......unless you use a helper column, e.g. in C2 you can extract the quarter number with

    =IF(A2="","",INT((MONTH(A2)+2)/3))

    copied down

    then you can use SUMIFS to sum for quarter 1 by using just

    =SUMIF(B:B,1,C:C)

    Personally I think you could probably use SUMPRODUCT just as easily, you can use a range like A2:A1000 to cater for expanding data (or even use dynamic ranges which will expand automatically to include all data).....or are you saying you have errors in the data? (in which case you can probably use an array formula to filter those out....or better to suppress those at source if you can)

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

    Re: Sum Quarters using SUMIFS rather than SUMPRODUCT

    daddylonglegs

    Many thanks for your help. I ended up using a helper column with SUMIFS. I could probably adapt my data and continue to use my SUMPRODUCT formula, but I need to do a bit of work on the data set to make it dynamic. I had to put a quick demo together for a client and figured it would be quicker to use SUMIFS's... Still it has been interesting understanding the limitations of both routes.

  10. #10
    Registered User
    Join Date
    08-30-2016
    Location
    Dover, DE
    MS-Off Ver
    2010
    Posts
    4

    Re: Excel 2007 : Sum Quarters using SUMIFS rather than SUMPRODUCT

    Dead thread revival:

    Thank you Hangman for asking your question and thank you DaddyLongLegs for answering. I was able to use your discussion, example, and formulas to solve some unrelated issues that I have been stumped by for a week.

+ 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