# Faster alternative to SUMPRODUCT?

1. ## Faster alternative to SUMPRODUCT?

I have 340 different sumproduct formulas a worksheet. and 31 worksheets.

heres one of them:
SUMPRODUCT(--(\$A\$2:\$A\$5000=I\$1),--(\$D\$2:\$D\$5000=\$G3))

is there something that will do the samething but faster?

also, i only want an answer displayed if it is greater than 0 but less than a number in a cell. Heres what i did:
if(0<SUMPRODUCT(--(\$A\$2:\$A\$5000=I\$1),--(\$D\$2:\$D\$5000=\$G3))<I\$2, SUMPRODUCT(--(\$A\$2:\$A\$5000=I\$1),--(\$D\$2:\$D\$5000=\$G3)), "")

This doesnt work for me.

edit: i got the if function working fine. the only problem i have is that there are so many formulas in my workbook. Im making a workbook to help track inventory, and there are 31 sheets for each day of the month. the location of the equipment on the 1st is linked to the location of the equipment on the 2nd, is linked to.....

so what i have found out is that when the location is updated on the 1st, all formulas on that sheet are recalculated and all the formulas on future dates are recalculated as well. updating inventory locations on the first take forever. but towards the end of the month, there are less formulas to compute, because updates to location only go forward, not backward. so it is a lot faster. my workbook is all done, but any input on making it faster would be greatly appreciated.

2. if(and(SUMPRODUCT(--(\$A\$2:\$A\$5000=I\$1),--(\$D\$2:\$D\$5000=\$G3))<I\$2,SUMPRODUCT(--(\$A\$2:\$A\$5000=I\$1),--(\$D\$2:\$D\$5000=\$G3))>0), SUMPRODUCT(--(\$A\$2:\$A\$5000=I\$1),--(\$D\$2:\$D\$5000=\$G3)), "")

Regards

Dav

3. Great! I appreciate your help, but is there a formula that will do the same thing as what you posted, only faster? I have a LOT of formulas. Actually, I have 11,780 sumproduct formulas. Ive been told sumproduct is slow, and im seeing it now. I was also told there are other options instead of sumproduct. I just dont know what they are.

4. Originally Posted by speakers_86
Great! I appreciate your help, but is there a formula that will do the same thing as what you posted, only faster? I have a LOT of formulas. Actually, I have 11,780 sumproduct formulas. Ive been told sumproduct is slow, and im seeing it now. I was also told there are other options instead of sumproduct. I just dont know what they are.

Good news is i got it going faster.

old formula
SUMPRODUCT(--(\$A\$2:\$A\$5000=I\$1),--(\$D\$2:\$D\$5000=\$G3))
new formula
SUMPRODUCT(--(\$A\$2:\$A\$200=I\$1),--(\$D\$2:\$D\$200=\$G3))

5. Yes shortening the ranges makes things a whole lot quicker, but it would have been hard for me to come up with that as an alternative, As I assumed you had correctly defined the ranges

Regards

Dav

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1