# Calculating Cells

#### jmersing

##### Well-known Member
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

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?

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

all are rows 131:2136

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)

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

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.

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

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.?

Replies
16
Views
630
Replies
1
Views
333
Replies
0
Views
484
Replies
1
Views
220
Replies
5
Views
411

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

### 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.

### Which adblocker are you using?

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

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