"Calculating Cells" taking way too long due to SumProduct

[Saladsamurai]

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"))

[Bob Phillips]

Should do.

[arkusM]

=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"))

[Bob Phillips]

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

[arkusM]

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

[Bob Phillips]

I am surprised that you didn't find it. I jusy Googled SUMPRODUCT and it came in at #4 in the list.

[arkusM]

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

[Bob Phillips]

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!

[Tinbendr]

...you probably wouldn't be asking in the forums in the first place. Catch-22!

Like they say, 'Misery loves company'.