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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

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,210
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,210
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,210
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,210
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.?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,508
Messages
5,832,154
Members
430,113
Latest member
CoeurDeLion

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