+ Reply to Thread
Results 1 to 4 of 4

SUMIFS or SUMPRODUCT Between Criteria

  1. #1
    Registered User
    Join Date
    05-26-2009
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    36

    SUMIFS or SUMPRODUCT Between Criteria

    Hi All

    I have a large data sheet currently hitting 70000 rows and growing.

    I am currently running a SUMPRODUCT formula to meet my needs, but this grinding the calculation to halt. currently 30 minutes to calculate.

    An example if the formulas i am running is:

    =SUMPRODUCT(--(Schedule!$A$2:$A$80000>=C2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="man"),Schedule!$E$2:$E$80000)-SUMPRODUCT(--(Schedule!$A$2:$A$80000>=D2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="man"),Schedule!$E$2:$E$80000)+SUMPRODUCT(--(Schedule!$A$2:$A$80000>=C2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$D$2:$D$80000="man"),Schedule!$E$2:$E$80000)-SUMPRODUCT(--(Schedule!$A$2:$A$80000>=D2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$D$2:$D$80000="man"),Schedule!$E$2:$E$80000)-SUMPRODUCT(--(Schedule!$A$2:$A$80000>=C2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="MAN"),--(Schedule!$D$2:$D$80000="LGW"),Schedule!$E$2:$E$80000)+SUMPRODUCT(--(Schedule!$A$2:$A$80000>=D2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="MAN"),--(Schedule!$D$2:$D$80000="LGW"),Schedule!$E$2:$E$80000)-SUMPRODUCT(--(Schedule!$A$2:$A$80000>=C2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="MAN"),--(Schedule!$D$2:$D$80000="LTN"),Schedule!$E$2:$E$80000)+SUMPRODUCT(--(Schedule!$A$2:$A$80000>=D2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="MAN"),--(Schedule!$D$2:$D$80000="LTN"),Schedule!$E$2:$E$80000)-SUMPRODUCT(--(Schedule!$A$2:$A$80000>=C2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="MAN"),--(Schedule!$D$2:$D$80000="BHX"),Schedule!$E$2:$E$80000)+SUMPRODUCT(--(Schedule!$A$2:$A$80000>=D2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="MAN"),--(Schedule!$D$2:$D$80000="BHX"),Schedule!$E$2:$E$80000)-SUMPRODUCT(--(Schedule!$A$2:$A$80000>=C2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="MAN"),--(Schedule!$D$2:$D$80000="EMA"),Schedule!$E$2:$E$80000)+SUMPRODUCT(--(Schedule!$A$2:$A$80000>=D2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="MAN"),--(Schedule!$D$2:$D$80000="EMA"),Schedule!$E$2:$E$80000)-SUMPRODUCT(--(Schedule!$A$2:$A$80000>=C2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="MAN"),--(Schedule!$D$2:$D$80000="LBA"),Schedule!$E$2:$E$80000)+SUMPRODUCT(--(Schedule!$A$2:$A$80000>=D2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="MAN"),--(Schedule!$D$2:$D$80000="LBA"),Schedule!$E$2:$E$80000)

    The main problem is speed.

    I had originally set the formulas to run by column range e.g. A:A, but was advised to to specify a range. I set the range to 80000 and this did work in terms of improving calculation, but still frustrates me.

    I want to know if there is a way to control the range that the formula looks for instead of it looking at all 80000 row's.

    so simplifying the above, the core calulation is:

    =SUMPRODUCT(--(Schedule!$A$2:$A$80000>=A1),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="man"),Schedule!$E$2:$E$80000)-SUMPRODUCT(--(Schedule!$A$2:$A$80000>=B1),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="man"),Schedule!$E$2:$E$80000)

    A1 = 01 Jan 13
    B1 = 08 Jan 13

    I am asking the formula to return a weeks worth of data, to do this i am summing all criteria that is greater/equal that 01 Jan 13
    then subtracting all criteria that is greater/equal that 08 Jan 13

    I essentially double count.

    I was thinking that if i could dynacmically set a >between< date range then the performance would improve?

    open to suggestions, would SUMIFS or another formula be any faster?

    many thanks

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: SUMIFS or SUMPRODUCT Between Criteria

    Not quite sure what you are trying to do, but im pretty sure that sumifS() will be quicker than sumproduct in this instance
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

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

    Re: SUMIFS or SUMPRODUCT Between Criteria

    Quote Originally Posted by Trig79 View Post
    =SUMPRODUCT(--(Schedule!$A$2:$A$80000>=A1),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="man"),Schedule!$E$2:$E$80000)-SUMPRODUCT(--(Schedule!$A$2:$A$80000>=B1),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="man"),Schedule!$E$2:$E$80000)
    With SUMIFS that can be

    =SUMPRODUCT(Schedule!$E:$E,Schedule!$A:$A,">="&A1,Schedule!$A:$A,"<"&B1,Schedule!$B:$B,"XXX",Schedule!$C:$C,"man")

    You should be able to use the whole column with SUMIFS because unlike SUMPRODUCT it's "clever" enough to only use the "used range" even when you specify the whole column
    Audere est facere

  4. #4
    Registered User
    Join Date
    05-26-2009
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: SUMIFS or SUMPRODUCT Between Criteria

    Hi

    thanks for help in general SUMIFS is quicker than SUMPRODUCT.

    FDibbins: what i am calculating is forecast data for the next 18 months, presenting it on a weekly basis eg: A1=01/01/13 B1=08/01/13 C1=15/01/13 and so forth.

    Data is 5 columns wide but approx 70000 rows long.

    i think the spreadsheet is probably about as efficient as it can be at this stage.

    thanks both

+ 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. SUMIFS or SUMPRODUCT with multiple criteria including date ranges
    By baxcat in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-25-2013, 09:40 AM
  2. [SOLVED] Sumifs or sumproduct with complex criteria
    By Alexander_Golinsky in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-08-2013, 03:03 AM
  3. Replies: 2
    Last Post: 01-23-2013, 06:25 AM
  4. Replies: 3
    Last Post: 11-21-2012, 04:57 PM
  5. [SOLVED] sumproduct and sumifs using multiple criteria across worksheets
    By mick86 in forum Excel General
    Replies: 4
    Last Post: 08-29-2012, 02:51 PM

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