+ Reply to Thread
Results 1 to 5 of 5

Faster alternative to SUMPRODUCT?

  1. #1
    Registered User
    Join Date
    07-27-2006
    Posts
    68

    Faster alternative to SUMPRODUCT?

    I have 340 different sumproduct formulas a worksheet. and 31 worksheets.


    heres one of them:
    SUMPRODUCT(--($A$2:$A$5000=I$1),--($D$2:$D$5000=$G3))

    is there something that will do the samething but faster?

    also, i only want an answer displayed if it is greater than 0 but less than a number in a cell. Heres what i did:
    if(0<SUMPRODUCT(--($A$2:$A$5000=I$1),--($D$2:$D$5000=$G3))<I$2, SUMPRODUCT(--($A$2:$A$5000=I$1),--($D$2:$D$5000=$G3)), "")

    This doesnt work for me.



    edit: i got the if function working fine. the only problem i have is that there are so many formulas in my workbook. Im making a workbook to help track inventory, and there are 31 sheets for each day of the month. the location of the equipment on the 1st is linked to the location of the equipment on the 2nd, is linked to.....

    so what i have found out is that when the location is updated on the 1st, all formulas on that sheet are recalculated and all the formulas on future dates are recalculated as well. updating inventory locations on the first take forever. but towards the end of the month, there are less formulas to compute, because updates to location only go forward, not backward. so it is a lot faster. my workbook is all done, but any input on making it faster would be greatly appreciated.
    Last edited by speakers_86; 10-02-2006 at 12:47 PM.

  2. #2
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    if(and(SUMPRODUCT(--($A$2:$A$5000=I$1),--($D$2:$D$5000=$G3))<I$2,SUMPRODUCT(--($A$2:$A$5000=I$1),--($D$2:$D$5000=$G3))>0), SUMPRODUCT(--($A$2:$A$5000=I$1),--($D$2:$D$5000=$G3)), "")

    Regards

    Dav

  3. #3
    Registered User
    Join Date
    07-27-2006
    Posts
    68
    Great! I appreciate your help, but is there a formula that will do the same thing as what you posted, only faster? I have a LOT of formulas. Actually, I have 11,780 sumproduct formulas. Ive been told sumproduct is slow, and im seeing it now. I was also told there are other options instead of sumproduct. I just dont know what they are.

  4. #4
    Registered User
    Join Date
    07-27-2006
    Posts
    68
    Quote Originally Posted by speakers_86
    Great! I appreciate your help, but is there a formula that will do the same thing as what you posted, only faster? I have a LOT of formulas. Actually, I have 11,780 sumproduct formulas. Ive been told sumproduct is slow, and im seeing it now. I was also told there are other options instead of sumproduct. I just dont know what they are.


    Good news is i got it going faster.

    old formula
    SUMPRODUCT(--($A$2:$A$5000=I$1),--($D$2:$D$5000=$G3))
    new formula
    SUMPRODUCT(--($A$2:$A$200=I$1),--($D$2:$D$200=$G3))


  5. #5
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    Yes shortening the ranges makes things a whole lot quicker, but it would have been hard for me to come up with that as an alternative, As I assumed you had correctly defined the ranges


    Regards

    Dav

+ 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