Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Freezing on SumIf
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Freezing on SumIf
My only guess is you're not giving Excel enough time to recalculate. Sounds
like it might take a while. -- Jim "Kimmerz321" wrote 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...o&userid=24105 | View this thread: http://www.excelforum.com/showthread...hreadid=513397 | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Freezing on SumIf
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...o&userid=31655 View this thread: http://www.excelforum.com/showthread...hreadid=513397 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Freezing on SumIf
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 "fselker" wrote in 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...o&userid=31655 | View this thread: http://www.excelforum.com/showthread...hreadid=513397 | |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Freezing on SumIf
I will dupe out the data and send you the file. I'd love to get this fixed in a future release - its been such a pain. I think I'm going to end up dumping my data into Access and doing some queries to do the math there. -- Kimmerz321 ------------------------------------------------------------------------ Kimmerz321's Profile: http://www.excelforum.com/member.php...o&userid=24105 View this thread: http://www.excelforum.com/showthread...hreadid=513397 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Freezing on SumIf
Would you like me to e-mail you mine with the similar problem?
"Jim Rech" wrote: 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 "fselker" wrote in 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...o&userid=31655 | View this thread: http://www.excelforum.com/showthread...hreadid=513397 | |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Freezing on SumIf
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Freezing on SumIf
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
help with "criterea" in the sumif function | Excel Worksheet Functions |