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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

jmersing

Well-known Member
Joined
Apr 14, 2004
Messages
887
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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?
 

jmersing

Well-known Member
Joined
Apr 14, 2004
Messages
887

ADVERTISEMENT

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

all are rows 131:2136
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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)
 

jmersing

Well-known Member
Joined
Apr 14, 2004
Messages
887

ADVERTISEMENT

Thanks so much Aladin, I guess using the sumproduct formulas were just the wrong approach?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Thanks so much Aladin, I guess using the sumproduct formulas were just the wrong approach?

Too many of them with many terms can be a burden in calculation efficiency.
 

jmersing

Well-known Member
Joined
Apr 14, 2004
Messages
887
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,596
Messages
5,597,081
Members
414,121
Latest member
DamianX88

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
Top