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.

 

[perrytodds]

Something happened to my post, the formula should read:

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

Sorry

 

[Chitosunday]

Well, that is the limitation of a spreadsheet. The only workaround i could think of is set your calculation to manual (tools,options, calculation, manual) then press f9 to recalculate. Second, if the computation is permanent, try to convert it to values (copy, pastespecial, values).

 

[Aladin Akyurek]

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

...

Is the date in ascending order?

 

[perrytodds]

It doesn't have to be, but the date could definitely be sorted easy enough. Basically, it is just a place where people put in the date, description, category, and source of funds per transaction, and the other sheets determine if it is a debt payment, from what checking account, and what type of line item expense it is (electricity, mortgage, food, etc...) by using the category and source of funds columns. The date column is only used so that the other spreadsheets can total the transactions in the correct month as they occur. It is nothing more than a data warehouse, so it could be sorted anyway you like. That was what I liked so much about the sumproduct function, but I guess I have made the sheet too big (5 year projection).

 

[Aladin Akyurek]

It doesn't have to be, but the date could definitely be sorted easy enough. Basically, it is just a place where people put in the date, description, category, and source of funds per transaction, and the other sheets determine if it is a debt payment, from what checking account, and what type of line item expense it is (electricity, mortgage, food, etc...) by using the category and source of funds columns. The date column is only used so that the other spreadsheets can total the transactions in the correct month as they occur. It is nothing more than a data warehouse, so it could be sorted anyway you like. That was what I liked so much about the sumproduct function, but I guess I have made the sheet too big (5 year projection).

Great.

1] Create an additional column that concatenates Category and Fund_Source. Name new range CatFund for example. Use a formula like:

=X&","&Y

where X is the first Category cell and Y the first Fund_Source cell.

2] Sort the area consisting of Date, Category, Fund_Source, CatFund, and Amount on Date in ascending order.

3] Switch to SumIf...

X1:

=MATCH(MAX(MIN(Date),C1),Date,1)

Y1:

=MATCH(D1,Date,1)

=SUMIF(INDEX(CatFund,X1):INDEX(CatFund,Y1),A3&","&A2,INDEX(Amount,X1):INDEX(Amount,Y1))

which replaces:

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


Would you please report back on the effect on performance?

 

[just_jon]

As I can atest from 2 years ago when Aladin 1st showed me this work around on another project, it will be staggering... remember thisone, Aladin?

 

[Aladin Akyurek]

As I can atest from 2 years ago when Aladin 1st showed me this work around on another project, it will be staggering... remember thisone, Aladin?

All that happened off-line, no?

 

[just_jon]

Most; it was that enormous file, detailing faaults in the Denver city's curbs. Bunch of GPS data that went on forever.

I don't know why neither of us ever posted the final solution, other than maybe so much was via PM/email that it would have taken a book to get everyone up to date.

It was this same situation, though - sort first, determine start/stop rows, then sumif with double INDEX.

 

[perrytodds]

Spending Plan - v3.xls
	A	B	C	D	E	F	G	H	I
1	Credit Card Roll Forward			12/1/2004	1/1/2005	2/1/2005	3/1/2005	4/1/2005	5/1/2005
2		Checking 1
3			Principal	5,000.00	(120.00)	(10,870.00)	(31,575.00)	(52,080.00)	(51,580.00)
4	18.00%		Interest Income	10.00	-	-	-	-	-
5			Deposits	1,250.00	1,250.00	-	200.00	500.00	500.00
6			Withdrawals	(6,380.00)	(12,000.00)	(20,705.00)	(20,705.00)	-	-
Checking and Savings

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