Speed Up calculations - sumproduct

[Risels7]

Hi,

I have about 20 formulas, most using SUMPRODUCT, next to a database of around 14500 rows. The problem I have is it's taking about 2 minutes to calculate each time. I have read up on speeding up the process and have been told to replace them with COUNTIFS or SUMIFS, but when I do I get an error message.
I'm Guessing this is because they are array formulas?

=SUMPRODUCT((LName=$AB2)*(Setone=2),--(Settwo<2))

=SUMPRODUCT(--(WName=AB2),--(LRank>=11),--(LRank<=20))

=SUMPRODUCT((WName=$AB2)*(Setone=2),--(Settwo=0)*(wodd
=SUMPRODUCT((LName=AB2)*(WRank>=10)*(WRank<=20)*SIGN((score1=6)+(score1=7))*SIGN((score3=6)+(score3=7))*((score2+score4)=AS4))


Here is a few of the formulas I am using, if anybody could help change a couple, I can work out the pattern and fix the rest, and hopefully speed up my workbook.

I appreciate any help i can get.

Thanks

[Teethless mama]

SUMPRODUCT() is an *array formula* without array enter which means you press Enter normally without using combination Ctrl+Shift+Enter keys. That function will slow down your spread sheet.

You can not use COUNTIFS(), or SUMIFS() when you use an older version Excel prior to XL-2007.

To help you better... We would like to see your spread sheet. So we can try to come up with efficient/short/elegant formula as much as possible.

[Tony Valko]

This may help a little bit...

Change this one:

=SUMPRODUCT((LName=$AB2)*(Setone=2),--(Settwo<2))

To:

=SUMPRODUCT(--(LName=$AB2),--(Setone=2),--(Settwo<2))

This one is incomplete but use the same general syntax as the formula above:

=SUMPRODUCT((WName=$AB2)*(Setone=2),--(Settwo=0)*(wodd

Change this one:

=SUMPRODUCT((LName=AB2)*(WRank>=10)*(WRank<=20)*SIGN((score1=6)+(score1=7))*SIGN((score3=6)+(score3=7))*((score2+score4)=AS4))

To:

=SUMPRODUCT(--(LName=AB2),--(WRank>=10),--(WRank<=20),--ISNUMBER(MATCH(score1,{6,7},0)),--ISNUMBER(MATCH(score3,{6,7},0)),--(score2+score4=AS4))

[Risels7]

Thanks...

So basically I have to Ctrl+Shift+Enter on all the formulas, and they should speed up?

[Risels7]

Thanks Tony..

I'll try that out.

[Teethless mama]

Thanks...

So basically I have to Ctrl+Shift+Enter on all the formulas, and they should speed up?

No. You're missing my point. When you calculate large spread sheet with lot of rows and/or columns try to avoid SUMPRODUCT() as much as possible. Try to use DBASE functions instead.

[Risels7]

Ok, I get you now...

So I should be replacing SUMPRODUCT with DSUM? if so, how would I go about editing this code with that?

=SUMPRODUCT((LName=$AB2)*(Setone=2),--(Settwo<2))

Thanks for taking the time to help.