How to reduce the processing time of Sumproduct?

1
votes

Introduction: I have a spreadsheet that is populated every daily based on the values of another worksheet. Currently the cells are being populated using a SUMPRODUCT formula. This is a piece of a larger process in which I intend to automate using VBA.

Formula example: 

 =SUMPRODUCT(((TEXT(crstage!$W$3:$W$23625,"###")=TEXT(credits!$A4,"###"))*(crstage!$X$3:$X$23625)*(TEXT(B$3,"yymmdd")=LEFT(crstage!$F$3:$F$23625,6))))

Problem: The processing time for the SUMPRODUCT formula is too long for my current needs. The numbers for previous days are subject to change therefore I have to keep the SUMPRODUCT formula in every applicable cell.

Worksheets: I have two worksheets, "credits" and "crstage". I have provided mock versions of the credit and crstage worksheets. The worksheets are significantly larger.

Credits: Total Daily credits for a given day. This worksheet is overwritten every day with a new set of data. This new set of data can change the amounts of the previous day.

enter image description here

crstage: A running total of credits. enter image description here

Question: Utilizing VBA how can I avoid lengthy processing time?

1
vbaexcel
Apart from the main question is it intentional that crstage!$X$2:$X$23625 starts from 2 row and other references from 3rd? – TomJohnRiddle
do you really have 23,000+ lines of data? Is that stagnate, or does it change regularly? – Scott Craner
Then lessen the number of rows it is is iterating to the actual data. For each additional row the sumproduct is doing multiple calculations as it created the arrays against which it iterates. It may not seem like much but a couple hundred lines is close to a million extra calculations. – Scott Craner
Agree with Scott, but could you not use a pivot table? – SJR
Using helper columns for the first TEXT part and the last LEFT part would probably speed things up and allow you to use SUMIFS which should be a little faster. It would also simplify using a pivot table and some GETPIVOTDATA formulas. – Rory

1 Answers

1
votes

Can you make it work like how I have it setup in my screenshot below? See the formula bar for the key formula.

enter image description here

Formula in B3 is =DATE("20"&LEFT(A3,2), MID(A3,3,2), MID(A3,5,2))

Edit: Pivot Table solution can be seen in image below:

enter image description here