Consulting

Results 1 to 9 of 9

Thread: "Calculating Cells" taking way too long due to SumProduct

  1. #1

    "Calculating Cells" taking way too long due to SumProduct

    Hey guys and gals

    I am having problems with my WorkBook freezing (Not Responding in Task Manager) and I have a feeling it is due to my SumProduct formulas.

    It freezes up whenever I F9 to manually calculate cells. I had switched to manual calculations because they were taking so long.

    I have this formuala
    =SUMPRODUCT((ISNUMBER('Cold Difference'!$1:$300))*...
    ...('Cold Difference'!$1:$300<=Charts!D9)*('Cooling Type'!$B$1:$B$300="T"))
    and about 4 or 5 others just like it in one of my worksheets. They have been fill-handled down so that there are really like 50 cells that contain this formula (but due to the fill handle there is generally a one cell difference in each formula).

    As you can see, the formulas references a decent-sized range..not huge, but decent.

    Actually now that I mention it, it seems that there is no length limit on the "Rows" I have indicated.

    I know that they only go up to EZ at the most on sheet "Cold Difference"

    How can I change this to prevent Excel from checking the entire row and only go up to EZ?

    I think this should work right?

    =SUMPRODUCT((ISNUMBER('Cold Difference'!$A$1:$EZ$100))*...
    ...('Cold Difference'!$A$1:$EZ$100<=Charts!D2)*('Cooling Type'!$B$1:$B$100="T"))
    Last edited by Saladsamurai; 09-28-2009 at 07:11 AM.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Should do.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular arkusM's Avatar
    Joined
    May 2007
    Location
    Calgary
    Posts
    52
    Location
    =SUMPRODUCT((ISNUMBER( 'Cold Difference'!$A$1:$EZ$100))*...
    ...( 'Cold Difference'!$A$1:$EZ$100<=Charts!D2)*('Cooling Type'!$B$1:$B$100="T"))
    You are not really using the SUMPRODUCT "correctly" (it seems to me), you should have comma's not *'s. the *'s make the sumproduct act like a simple sum() formula that would be confirmed with CTRL+SHIFT+ENTER.
    I am not 100% sure if this will make a performance issue but you might as well use SUMPRODUCT() product correctly. It seems to be doing the same thing but with the *'s it is not using the native functions of SUMPRODUCT.

    Not sure what the specs on your computer are, but I have used Sumproducts on WAY WAY more 6500 cells of data (EZ Col = [5*26 letters]*50rows =6500 cells) and I have had no trouble with freeze-ups.

    Try replacing the *'s with ","'s in the formula:
    =SUMPRODUCT((ISNUMBER( 'Cold Difference'!$A$1:$EZ$100)),( 'Cold Difference'!$A$1:$EZ$100<=Charts!D2),('Cooling Type'!$B$1:$B$100="T"))
    Excel 2003, WinXP

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I think you have missed 10 years of development which has mainly centred on SUMPRODUCT. See http://xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Regular arkusM's Avatar
    Joined
    May 2007
    Location
    Calgary
    Posts
    52
    Location
    Quote Originally Posted by xld
    I think you have missed 10 years of development which has mainly centred on SUMPRODUCT. See http://xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation
    I though I knew something about SUMPRODUCT.... I just been school'd LOL.

    Indeed I wish my searching on SUMPRODUCT a year or so ago turned up that page you posted (Now bookmarked)... I use SUMPROD a lot. I did not know you could use the "+" as an OR operator...

    Cheers
    Excel 2003, WinXP

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I am surprised that you didn't find it. I jusy Googled SUMPRODUCT and it came in at #4 in the list.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Regular arkusM's Avatar
    Joined
    May 2007
    Location
    Calgary
    Posts
    52
    Location
    Haha, no doubt, knowing the non linear way my mind "works" I probably took a very circuitous route to find sumproduct. Most days I wonder how I get anything done or find anything. I have a gift of not always being able to find the obvious, but the minute detail... Also I probably was not searching for "sumproduct". In fact it is one of the proverbial issues with learning, like the job posting that says they want 5 years experience, which is nice but I need to have the job to get the five years, no one starts with 5 years experience... When I first learned about sumproduct I had an end goal in mind and thus did not know what to search for... Actually it is one of the reasons I like the KB and Articles here is they present ways of doings things I did not know could be done and very creative methods of doing things.

    Thanks for providing such a great resource, with your link. And Contributing so many quality answers to questions.
    Cheers.

    Mark
    Excel 2003, WinXP

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Yeah, it is a bit like asking questions on these type of forums. You ask a question, and someone says, Google is your friend. If you knew how to frame a question to Google you probably wouldn't be asking in the forums in the first place. Catch-22!
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    VBAX Expert Tinbendr's Avatar
    Joined
    Jun 2005
    Location
    North Central Mississippi (The Pines)
    Posts
    993
    Location
    Quote Originally Posted by xld
    ...you probably wouldn't be asking in the forums in the first place. Catch-22!
    Like they say, 'Misery loves company'.

    David


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •