Problem with SUMPRODUCT

[perrytodds]

I am having a problem with the workbook I am working in. I have written a workbook that is to be a budget tool for people who need help managing their finances. I have created a worksheet that records all of their transactions, and then several additional spreadsheets that pull information from the transaction sheet. I have written most of the cells to be a SUMPRODUCT formula such as

SUMPRODUCT(--(Date>=B1),--(Date<C1),--(Category=A3),--(Fund_Source=A2),Amount)

The formula works perfectly, but I have several hundred to maybe even a thousand of these formulas in one spreadsheet. Every time that I try to input something in the transactions page, the workbook tries to auto calculate, and it takes several minutes for the spreadsheet to recalculate all of the formulas. This spreadsheet is ultimately going to be used by people that are not very excel savvy, and I am worried that when it tries to calculate, and it takes several minutes to do so for every transaction entered (unless you turn auto calc off), that they will be turned off by it, and will get frustrated and stop using the spreadsheet.

I am asking for any possible solutions that will make the spreadsheet work faster, or if there is something I can do to the formula above that will make it work faster. I understand that the double negative could be replaced by other means, but I don't know what they are, or if it will even make any difference.

I have also thought about writing a macro that would take all of the future months (this is a five year budget tool that I am building to help people plan to get out of debt) and copy values so that when you are entering transactions, the future periods are not calculating, and then when all of the transactions are entered, they can run the macro to auto fill all of the formulas in the future period and then copy values over the top. I just don't know how long it will take excel to run a macro of this type, and if I will just be getting myself into the same situation.

Please make any suggestions that may help, I greatly appreciate it.

 

[Aladin Akyurek]

...

I have entered the formula you had suggested, but I am having some issues with it that I can't seem to get around. First, H5 and I5 have the same value in it, and I5 should have 0 as the result. in the transaction log mentioned earlier, I have not input any data for the month of May. It is pulling the same result from April forward into May. Any thoughts on why this is being done? I have already made sure that the dates at the top of the sheet are correct, and that the transactions in the source data are sorted in ascending alpha numeric order. This exact formula works for another line in my spreadsheet for Checking 2 (not displayed), which is why I am so baffled. Please let me know if you see something wrong with the formula, or if I need to post more information, thanks in advance.

Todd

I think you need to add a test to check whether D1 <= MAX(Date).

 

[perrytodds]

I think you need to add a test to check whether D1 <= MAX(Date).[/quote]

I am not sure I know what you are getting at. Do I need to add something to my formula, or are you asking me if D1 is less than the Max date. I am gathering that you are wanting to add an if statement that says if I1 is > than Max(Date) then zero. Please let me know if I am interpreting your response correctly.

Thanks!

 

[Aladin Akyurek]

...

I am gathering that you are wanting to add an if statement that says if I1 is > than Max(Date) then zero. Please let me know if I am interpreting your response correctly.
...

Yes, that is what I was thinking of.

 

[perrytodds]

That did the trick for that problem, I will get all of the formulas updated, then let you know if the performance is better, thanks again for all of your help on this topic.