Excel ran out of resources while attempting to calculate one or more formulas

Question

I have a workbook to do 'smart'-graphs on my expenses. It's been running for a year and there are now a lot of graphs and expenses. Excel now throws an out-of-resources error whenever I change anything or open the workbook. Thing is, I have lots of resources and its not using hardly any of them.

Win8 64bit w/ 8 core CPU and 32GB of ram
Office 2013 64bit

I have 2 sheets, the first sheet called Expenses has 3 columns [Date,Description,Amount] and about 1500 rows of data. The second sheet has a LOT (500 or so) of formulas that are all the same and aim to do "Sum all expenses between date X and Y where description matches -some needle-". The formula I have is this:

=
ABS(
    SUMPRODUCT(
        --(Expenses!A:A >= DATE(2011,12,1)), 
        --(Expenses!A:A < DATE(2012,1,1)), 
        --(ISNUMBER(FIND(C50,Expenses!B:B))),
        Expenses!C:C
    )
)

Can I give Excel more resources? (I'm happy for it to use all my ram, and chug my CPU for a few minutes).

Is there a more efficient way I can do this formula?

I understand that this formula is creating a large grid and masking my expenses list with it, and that for each formula this grid has to get created. Should I create a macro to do this more efficiently instead? If I had a macro, I would want to call it from a cell somehow like

=sumExpenses(<startDate>, <endDate>, <needle>)

Is that possible?

Thanks.

Answer 1

I had a similar problem where there were a few array formulas down about 150 rows and I got this error, which really baffled me because there really aren't that many formulas to calculate. I contacted our IT guy and he explained the following, some of which I understand, most of which I don't:

Generally when the computer tries to process large amounts of data, it uses multi-threaded calculation, where it uses all 8 processors that the computer tricks itself into thinking it has. When multi-threaded calculation is turned off, the computer doesn't throw the 'Excel ran out of resources...' error.

To turn off multi-threaded calculation, got to the 'File' tab in your Excel workbook and select 'Options'. On the right side of the box that appears select 'Advanced' and scroll down to the heading 'Formulas'. Under that heading is a check box that says 'Enable multi-threaded calculation'. Untick it, then select 'OK' and recalculate your formulas.