sumproduct formula to slow

[Guest]

I created a worksheet using the sumproduct formula and it runs very slow.
15-25 minutes to calculate at every change. I guess its to big for my
computer to run ( the worksheet has 248 columns and 100 rows of formula)
Can I speed it up somehow? Or can I get I get match to do multipe criteria?

=SUMPRODUCT(--('A-L Data Table'!A$6:A$2496=$A10)*--('A-L Data
Table'!B$6:B$2496=$D3),'A-L Data Table'!$D$6:$D$2496)

Maybe something like =INDEX('A-L Data Table'!D$6$2500,MATCH($A10,'A-L Data
Table'!$A$6:$A$2500,0)*MATCH($D$3,'A-L Data Table'!$B$6:$B$2500,0),1)


Thanks,

Todd

 

[Frank Kabel]

Hi
a formula approach probably won't help you in this case. You may try using a
pivot table instead

 

[Guest]

Some tips

http://www.decisionmodels.com/


Regards,

Peo Sjoblom

 

[Aladin Akyurek]

You might want to invoke a SumIf formula instead...

Insert a new sheet that you could name Concat...

In A6 on Concat enter & copy down:

='A-L Data Table'!A$6&","&'A-L Data Table'!B$6

Then invoke:

=SUMIF(Concat!A$6:A$2496,$A10&","&$D3,'A-L Data Table'!$D$6:$D$2496)

This set up trades off cell space (memory) against speed (time).

Another option is to build an appropriate pivot table from your data.

 

[Guest]

Thank you all, very much you were very helpful as always.


Todd