Seeking Help - My spread sheet is SLOW!!!

[TCarrigan]

I am attempting to do more with Excel 2007 than it was probably meant to do. I have LARGE SUMPRODUCT command refferences and now my spreadsheet is very very slow.

My sheet includes many tabs that all refference back and forth to each other as well as a single entry with approx 500 dependents. The single entries are in approx 400 locations all with the same dependents. If I change the value in Precedents cell, the calculation take several seconds.

This example formula is repeated about 500+ times throughout the sheet.
=IF($BJ4="","",SUMPRODUCT(($D$66:$D$1703=BL$2)*($R$66:$R$1703=$BJ4)*($AA$66:$AA$1703)))

as well as this formula repeated hundreds of times:
=SUMPRODUCT(('Misc Cable Configurator'!$N$66:$N$1703='Itemized Report'!$D$33)*('Misc Cable Configurator'!$C$66:$C$1703='Itemized Report'!$D54)*('Misc Cable Configurator'!$E$66:$E$1703=1)*('Misc Cable Configurator'!G$66:G$1703)*('Misc Cable Configurator'!$E$66:$E$1703))+SUMPRODUCT(('Misc Cable Configurator'!$N$66:$N$1703='Itemized Report'!$D$33)*('Misc Cable Configurator'!$C$66:$C$1703='Itemized Report'!$D54)*('Misc Cable Configurator'!G$66:G$1703)*('Misc Cable Configurator'!$E$66:$E$1703>1)*(1))+SUMPRODUCT(('Misc Cable Configurator'!$N$66:$N$1703='Itemized Report'!$D$33)*('Misc Cable Configurator'!$C$66:$C$1703='Itemized Report'!$D54)*('Misc Cable Configurator'!G$66:G$1703)*('Misc Cable Configurator'!$E$66:$E$1703>1)*('Misc Cable Configurator'!$E$66:$E$1703-1)*'Misc Cable Configurator'!$CD$3)

I'm totally lost on how to speed this thing up.

Thanks in advance.

 

[DonkeyOte]

And I'm not ignoring FastExcel whose site & knowledge is legendary

 

[mgirvin]

Dear DonkeyOte,

That is an awesome tip.
I have used concatenated columns for dealing with duplicates in lookup situations, but it never occurred to me to use concatenated columns to deal with large multi-conditional formulas.

And your example you submitted is so well constructed and easy to understand!!!

Thanks for the new power tool that I am adding to my Excel tool kit!

 

[350Z Pilot]

Thank you Donkey.
I will look to see if I can incorporate your suggestions.
Unfortunately most of my calculations are not dependent on each other (except for the helper calcs). So I'm not quite sure if I can use your suggestion in its present form but I may be able to adapt it.