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

"fselker" wrote:

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


"Kimmerz321" wrote:


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...o&userid=24105
View this thread: http://www.excelforum.com/showthread...hreadid=513397