Too Many Sumproducts Freezing Up Excel?

[nila.khurana]

Hi Guys,

First post both here and Mr. Excel, sorry for any missed protocol. I have a workbook wherein one sheet has a column with dates, a column with people's names and columns with times people clocked in and out. On another sheet in the workbook is a column of all the transactions and columns of times and columns of days those transactions were posted. On a third sheet I listed a column of dates, then used sumproduct to match people that clocked in and out on each particular date and pull out the times. I possibly could have done this with vlookup if each person had their own sheet but for now since I'm trying to pull out both the person and date I only know how to do this using sumproduct. Using another sumproduct formula I'm counting all the transactions that occurred between the in and out time for a particular person, so that in the end I have a list of each date, along with the times each person clocked in and out and the total of transactions during that time period.

The problem is, while this seems to work at the basic level, it keeps crashing my computers. I see all 8 of my processors running at full capacity trying to process this, and then I get an error that I ran out of memory. Some of the calculations appear there, but others are not filled in where they should be. If I restart the computer, just open excel, the same thing happens. I'm on Windows 8 with an i7 haswell laptop and 8 gigs of ram. The file is only 450kb, with like a max of 400 rows on each sheet. I did autofill the sumproducts down to 365 rows for a full year, and there are maybe 8 people that I am pulling this information on.

Let me know if there's anything else I can provide on this. Thanks,

Nila

[FDibbins]

Hi, welcome to the forum

1. in your SP formulas, are you using full column ranges? (A:A etc) If so, change them to cover just the ranges you need...A1:A400
2. Have you considered using some of the other, more efficient functions excel 2010 provides?
- sumif/sumifS
- countif/countifS
- averageif/averageifS
(among others)

Can you provide a few samples of your SP formulas, or, better yet, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[nila.khurana]

Hi,

Thanks for your quick reply on this. I took a bit to convert all the sumproduct to sumifs and scaled back the ranges to 50000 rows instead of the entire column and yes that did help quite a bit. Now it's not giving me a memory error, and even on a less powerful computer I'm able to get my results. Basically now if I copy and paste 1500 rows of new data I just have to wait a minute for it to compute, but it is working and that's the important thing. If it helps someone going forward I could upload samples of the work, but I would have to take some time to clean the file up first so for now I'll share the formulas I used.

=SUMIFS(Data!$F$2:$F$50000,Data!$A$2:$A$50000,$A3,Data!$B$2:$B$50000,">"&(SUMIFS(Master!$AH$2:$AH$10000,Master!$X$2:$X$10000,B$1,Master!$Y$2:$Y$10000,B$2,Master!$AG$2:$AG$10000,$A3)),Data!$B$2:$B$50000,"<"&(SUMIFS(Master!$AI$2:$AI$10000,Master!$X$2:$X$10000,B$1,Master!$Y$2:$Y$10000,B$2,Master!$AG$2:$AG$10000,$A3)))

A3 is the date I'm gathering data for, which I have from 1/1/15 to 12/31/15.
B$1 is the person I am gathering info on.
B$2 is the department I am gathering info on as sometimes people work in 2 different ones.
The Master sheet contains AH and AI, the in and out time of the person, which I am using to search against the transactions in the larger portion of the formula. It also contains X the name person who clocked in, Y the department of the person, and AG the date of the clocking in and out.
The Data sheet contains transaction info (F is transaction total, A is date, B is time of transaction), so I'm adding total of transactions where the date matches what's written, and time of the transaction is between the start and end time of the person.

Thanks,

Nila

[FDibbins]

Im happy it helped out a bit for you

Another thing to consider/test, is using helper columns to combine some of those criteria. For instance, you could have a helper that combines the name and department, (not sure you could combine the date), and then use 2 criteria instead of 3.

Likewise, do the same combo on the other sheet, and Im not quite following the internal sumifs() you have there, but perhaps they could be in some sort of helper column too.

Another trick I have used with larger databases - if you have historic data that wont change, convert any formulas that use (just) that data, to values...every bit helps

Im not positive these will speed things up, but as you saw, the less complex formulas you have, the more efficient your process becomes.

Oh, and points I forgot to add...
- avoid the use of too many volatile formulas like TODAY()...if you use this in a lot of formulas, consider putting TODAY() in its own cell and referencing that cell. There is a good discussion about them here...
http://www.decisionmodels.com/calcsecretsi.htm
- avoid excessive use of Conditional Formatting, that can drag your file down to a standstill