Speed Up Excel Calculations

mdiaz

New Member
Joined
Oct 7, 2010
Messages
17
I am having issues with a spreadsheet I have created. I am using a SUMPRODUCT formula for several different columns to return a count of data meeting those criteria's specified. One formula in each cell is targeted for a cell that I want to see data on. For example I have Monday through Friday in a drop down box. As I select Monday all the formulas should update with that days data along with the other criteria for the formula. When making my selections this takes about 1 minute to recalculate. Can someone help me speed this up. I had a fried who did something similar to this and it worked fast however I am no longer in touch. Please help!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Depending on the number of SUMPRODUCT formulas, and their complexity, this could be expected. SUMPRODUCT is among the most ineffecient (but useful) formulas out there. Do you also have several lookup-style formulas or any array-entered formulas?
 

mdiaz

New Member
Joined
Oct 7, 2010
Messages
17
I just have the SUMPRODUCT and yes there are alot. I am not familiar with another way of getting at my data. If there is another way to pull in the data using some other formula I am all ears. Below is an example of the formuals I am using.

=SUMPRODUCT(--(Exception!I2:I45000="a"),--(Exceptions!G2:G45000<"12"),--(Exceptions!B2:B45000=A2),--(Exceptions!C2:C45000="11"))
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
What version of Excel do you use?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

I just have the SUMPRODUCT and yes there are alot. I am not familiar with another way of getting at my data. If there is another way to pull in the data using some other formula I am all ears. Below is an example of the formuals I am using.

=SUMPRODUCT(--(Exception!I2:I45000="a"),--(Exceptions!G2:G45000<"12"),--(Exceptions!B2:B45000=A2),--(Exceptions!C2:C45000="11"))

If 12 is text, then < "12" does not make much sense. Are you sure that you must put 11 and 12 between double quotes?
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

I am having issues with a spreadsheet I have created. I am using a SUMPRODUCT formula for several different columns to return a count of data meeting those criteria's specified. One formula in each cell is targeted for a cell that I want to see data on. For example I have Monday through Friday in a drop down box. As I select Monday all the formulas should update with that days data along with the other criteria for the formula. When making my selections this takes about 1 minute to recalculate. Can someone help me speed this up. I had a fried who did something similar to this and it worked fast however I am no longer in touch. Please help!
See this site for tips on efficiency.

http://www.decisionmodels.com/
 

mdiaz

New Member
Joined
Oct 7, 2010
Messages
17
I read this prior to submitting this thread and I did not help, thanks anyway.
 

Forum statistics

Threads
1,141,284
Messages
5,705,510
Members
421,399
Latest member
hjweiss00

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