+ Reply to Thread
Results 1 to 15 of 15

Converting a slow SUMPRODUCT formula to SUMIFS (or other)

  1. #1
    Registered User
    Join Date
    12-06-2012
    Location
    Barnsley, England
    MS-Off Ver
    2010
    Posts
    6

    Converting a slow SUMPRODUCT formula to SUMIFS (or other)

    I have a SUMPRODUCT formula which looks at three columns - ORDER, START DATE, HOURS OF WORK to determine negative values in the HOURS OF WORK columns so that I can delete the negative value along with the corresponding positive value.

    This worksheet can potentially have hundreds of thousands of rows so as you can image, the SUMPRODUCT solution takes an absolute age, to the point where it's actually quicker to do the job manually. From what I can find online, I believe a SUMIFS formula (even though it may require several extra columns) will complete the calculation much quicker.

    Here's what I have at the moment - works perfectly but is extremely slow:

    =IF((SUMPRODUCT(($A$2:$A$1000=A2)*($B$2:$B$1000=B2)*($C$2:$C$1000=-C2))>0)*(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2)*($C$2:$C2=C2))=1),"DELETE","")

    I've been trying to perform the same calculation with SUMIFS however I just can't get my head around it.

    If someone could help me out that would be amazing.

    Thanks.

  2. #2
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,373

    Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other)

    any possibility you can upload a small sample with the results included so people can work with the data?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,745

    Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other)

    Using SUMPRODUCT in this way looks that you need COUNTIFS function and not SUMIFS.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Registered User
    Join Date
    12-06-2012
    Location
    Barnsley, England
    MS-Off Ver
    2010
    Posts
    6

    Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other)

    Quote Originally Posted by Sambo kid View Post
    any possibility you can upload a small sample with the results included so people can work with the data?
    Yes, certainly. Thanks.

    SUMPRODUCT.xlsx

  5. #5
    Registered User
    Join Date
    12-06-2012
    Location
    Barnsley, England
    MS-Off Ver
    2010
    Posts
    6

    Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other)

    Quote Originally Posted by Fotis1991 View Post
    Using SUMPRODUCT in this way looks that you need COUNTIFS function and not SUMIFS.
    I gather what I need the formula(s) to be doing is comparing the positive and negative values which have a common date and order number, I'm afraid I just can't get my head around how to do that.

  6. #6
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    6,931

    Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other)

    Hi.

    I don't understand the need for the initial clause in your IF statement, i.e.:

    SUMPRODUCT(($A$2:$A$1000=A2)*($B$2:$B$1000=B2)*($C$2:$C$1000=C2))>0

    Surely this will always be TRUE, since there will always be at least one row which satisfies this condition, namely that in which the formula lies.

    Hence you can simply remove that check and use:

    =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2)*($C$2:$C2=C2))=1,"DELETE","")

    which is, even better:

    =IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2,$C$2:$C2,C2)=1,"DELETE","")

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  7. #7
    Registered User
    Join Date
    12-06-2012
    Location
    Barnsley, England
    MS-Off Ver
    2010
    Posts
    6

    Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other)

    Quote Originally Posted by XOR LX View Post
    I don't understand the need for the initial clause in your IF statement, i.e.:

    SUMPRODUCT(($A$2:$A$1000=A2)*($B$2:$B$1000=B2)*($C$2:$C$1000=C2))>0
    Thanks for the reply, I gather the initial part of the formula is crucial as it contains ($C$2:$C$1000=-C2) - minus C2

    If I only do =IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2,$C$2:$C2,C2)=1,"DELETE","") then it just gives me duplicate positive values for each start date and order.

  8. #8
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,373

    Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other)

    in your example I think I understand why you are marking rows 9 and 10 for delete but I'm not sure why you have row 11 marked for delete.

  9. #9
    Registered User
    Join Date
    12-06-2012
    Location
    Barnsley, England
    MS-Off Ver
    2010
    Posts
    6

    Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other)

    Quote Originally Posted by Sambo kid View Post
    in your example I think I understand why you are marking rows 9 and 10 for delete but I'm not sure why you have row 11 marked for delete.
    Row 11 would correspond to Row 16 - matching negative value on the same date and order number.

    Thanks.

  10. #10
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    6,931

    Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other)

    Quote Originally Posted by scottk82 View Post
    Thanks for the reply, I gather the initial part of the formula is crucial as it contains ($C$2:$C$1000=-C2) - minus C2
    Ah, apologies. I missed that small detail.

    Regards

  11. #11
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    6,931

    Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other)

    The equivalent would be:

    =IF(COUNTIFS($A:$A,A2,$B:$B,B2,$C:$C,-C2),IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2,$C$2:$C2,C2)=1,"DELETE",""),"")

    in which at least you can reference entire columns with no detriment to performance.

    I cannot guarantee that, over very large ranges, this set-up will not also suffer.

    Regards

  12. #12
    Forum Expert Sambo kid's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,373

    Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other)

    I was working out one with additional columns but it looks like XOR LX got it for you so, good luck.

  13. #13
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    6,931

    Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other)

    Quote Originally Posted by Sambo kid View Post
    I was working out one with additional columns but it looks like XOR LX got it for you so, good luck.
    May be worth posting, though, as a multi-column solution may prove to be more efficient.

    Regards

  14. #14
    Registered User
    Join Date
    12-06-2012
    Location
    Barnsley, England
    MS-Off Ver
    2010
    Posts
    6

    Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other)

    Quote Originally Posted by XOR LX View Post
    The equivalent would be:

    =IF(COUNTIFS($A:$A,A2,$B:$B,B2,$C:$C,-C2),IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2,$C$2:$C2,C2)=1,"DELETE",""),"")

    in which at least you can reference entire columns with no detriment to performance.

    I cannot guarantee that, over very large ranges, this set-up will not also suffer.

    Regards
    That's excellent, thank you.

    I have tested it over 10,000 rows and it took around a minute and a half to process - literally hundreds of times faster than the SUMPRODUCT formula.

    Thanks to everyone who took the time to look into this.

    Have a very merry Christmas.

  15. #15
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    6,931

    Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other)

    You're welcome! And Merry Christmas!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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