Saladsamurai
09-28-2009, 05:46 AM
Hey guys and gals :hi:
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"))
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"))