sumproduct formula to slow
 

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:D$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

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

--
Regards
Frank Kabel
Frankfurt, Germany
"Todd" schrieb im Newsbeitrag
...
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:D$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

Some tips

http://www.decisionmodels.com/


Regards,

Peo Sjoblom


"Todd" wrote:

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:D$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

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.

On Tue, 21 Dec 2004 13:47:09 -0800, "Todd"
wrote:

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:D$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

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


Todd


"Aladin Akyurek" wrote:

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.

On Tue, 21 Dec 2004 13:47:09 -0800, "Todd"
wrote:

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:D$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