Freezing on SumIf

[Kimmerz321]

I'm working with a rather large workbook (~50,000K). I have a page set
up that does a bunch of SumIfs to return results. I'm not sure when
this happened, but now, everytime I try to recalculate the formulas
after the data has been refreshed, the sheet locks up and I have to
ctrl+alt+delete out of it. I'm pulling the data I'm using in these
SumIfs from 4 sheets with a total of around 150,000 rows. Has anyone
ever heard of this problem, and is there anything I can do to fix it?
Suggestions? I'm at the end of my rope here.

 

[Jim Rech]

My only guess is you're not giving Excel enough time to recalculate. Sounds
like it might take a while.

--
Jim
in message |
| I'm working with a rather large workbook (~50,000K). I have a page set
| up that does a bunch of SumIfs to return results. I'm not sure when
| this happened, but now, everytime I try to recalculate the formulas
| after the data has been refreshed, the sheet locks up and I have to
| ctrl+alt+delete out of it. I'm pulling the data I'm using in these
| SumIfs from 4 sheets with a total of around 150,000 rows. Has anyone
| ever heard of this problem, and is there anything I can do to fix it?
| Suggestions? I'm at the end of my rope here.
|
|
| --
| Kimmerz321
| ------------------------------------------------------------------------
| Kimmerz321's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=24105
| View this thread: http://www.excelforum.com/showthread.php?threadid=513397
|

 

[fselker]

I have had same problem with Countif. It isn't just impatience -- when
I first paste the formulas into a grid they calculate the first time in
about 15 seconds. But if they ever need to recalculate, it freezes, and
I've left it for quite awhile waiting.

To get these answers I ultimately had to paste the formulas to get the
answers initially, then set calculation to manual so it wouldn't try to
recalculate and freeze, then replace the cells with their values, then
re-set calculation to automatic. I had to do this repeatedly and would
be interested in what the problem/solution is.

 

[Jim Rech]

Interesting. I've never heard of this problem, or encountered it obviously.
I'd love to try to reproduce it on my machine if you are at liberty to zip
the file and send it to me. If it reproduces in Excel 12 (I'm a beta
tester) I'll bug it and maybe it will get fixed.

--
Jim
message |
| I have had same problem with Countif. It isn't just impatience -- when
| I first paste the formulas into a grid they calculate the first time in
| about 15 seconds. But if they ever need to recalculate, it freezes, and
| I've left it for quite awhile waiting.
|
| To get these answers I ultimately had to paste the formulas to get the
| answers initially, then set calculation to manual so it wouldn't try to
| recalculate and freeze, then replace the cells with their values, then
| re-set calculation to automatic. I had to do this repeatedly and would
| be interested in what the problem/solution is.
|
|
| --
| fselker
| ------------------------------------------------------------------------
| fselker's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=31655
| View this thread: http://www.excelforum.com/showthread.php?threadid=513397
|

 

[Kimmerz321]

I will dupe out the data and send you the file. I'd love to get thi
fixed in a future release - its been such a pain. I think I'm going t
end up dumping my data into Access and doing some queries to do the mat
there

 

[fselker]

Would you like me to e-mail you mine with the similar problem?

 

[fselker]

In case you didn't get Jim's explanation of this:

Hi Frank-



COUNTIFs can be slow, but that is not the problem with your workbook. The
problem is that Excel's smart recalc is turned off by the sheer size of your
workbook and so it has to recalc almost every formula for every COUNTIF.



Excel normally keeps track of what cells each formula depends on and does
not recalc a formula unless a precedent cell has changed value. This is what
I mean by smart recalc. But when there are too many formulas or they are too
complex Excel stops doing this and recalcs cells whether they need it or not.
That's what you're seeing. Each time Excel calcs one of your COUNTIFs it
sees it depends on the range H17:H56693 so it first recalcs that range.
(When you do an F2, Enter, Excel just calcs that cell, not precedent ranges.)



But you can easily reduce the number of formulas and make your calcs far
faster. Select the range H17:H56693 and convert it from formulas to values
(First do a Copy then and then do an Edit, Paste Special, Values right in
place). Now put an "=" in front of the F3 formula and copy it and press F9.



FYI, Excel 12 (the version now in beta test) can keep track of more
dependencies so it calcs your sheets as is.



Jim Rech

 

[fselker]

I've done more testing, and I don't think this is the answer. More to follow.