Speed Up Excel Calculations

[mdiaz]

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!

 

[MrKowz]

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]

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]

What version of Excel do you use?

 

[Aladin Akyurek]

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?

 

[mdiaz]

Its 2000.

Yes it is all text.

 

[T. Valko]

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]

I read this prior to submitting this thread and I did not help, thanks anyway.

 

[Aladin Akyurek]

I read this prior to submitting this thread and I did not help, thanks anyway.

What is the answer to the question I posted?

 

[mdiaz]

Yes