Calculating Cells

jmersing

Well-known Member
Joined
Apr 14, 2004
Messages
887
I have a wowrkbook that has a few hundred sumproduct formulas in it. When I do certain things like cut a named range and move it somewhere else the file goes into "Calculating Cells" mode and takes about 10 seconds to complete. I'm thinking this is being caused by the sumproduct formlas, but I'm not sure.

Anyone have any advice on how to speed up these calculations other than turning auto calc on and off?

Thanks
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
I'm not quite sure I understand the example. Here is what one of my formulas look like, can you show how you would propose to change it?

=SUMPRODUCT(--(Site=R$9),--(PlanWeek=WEEK),--(Area=$B$154),--(Account=$C167),Retrievals!$N$131:$N$2136)

Thanks
 
Upvote 0
I'm not quite sure I understand the example. Here is what one of my formulas look like, can you show how you would propose to change it?

=SUMPRODUCT(--(Site=R$9),--(PlanWeek=WEEK),--(Area=$B$154),--(Account=$C167),Retrievals!$N$131:$N$2136)

Thanks

What are the ranges Site, PlanWeek, Area, and Account refer to?
 
Upvote 0
THey are all the same rows, the columns are:
Site -H
Plan Week - E
Area - K
Account - l

all are rows 131:2136
 
Upvote 0
THey are all the same rows, the columns are:
Site -H
Plan Week - E
Area - K
Account - l

all are rows 131:2136

In O2 enter and copy down:

=E2$","&H2","&I2&","&K2

Then invoke:

=SUMIF((Retrievals!$O$131:$O$2136,WEEK&",",&R$9&","&$C167&","$B$154,Retrievals!$N$131:$N$2136)
 
Upvote 0
Thanks so much Aladin, I guess using the sumproduct formulas were just the wrong approach?
 
Upvote 0
I just finished replacing all of my sumproduct formulas with Sumif formulas (11000 of them)

I'm still having the "calculating Cells" message on the status bar take about 10-15 seconds for each calc?

Any other suggestions to reduce this calc time, other than the obvious i.e. have auto calc off, use less formulas etc.

Thanks
 
Upvote 0
I just finished replacing all of my sumproduct formulas with Sumif formulas (11000 of them)

I'm still having the "calculating Cells" message on the status bar take about 10-15 seconds for each calc?

Any other suggestions to reduce this calc time, other than the obvious i.e. have auto calc off, use less formulas etc.

Thanks

Do you also have formulas with volatile functions like INDIRECT, OFFSET, etc.?
 
Upvote 0

Forum statistics

Threads
1,217,500
Messages
6,137,012
Members
450,038
Latest member
JackOfBlades

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top