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

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.

I had a go at creating a function that hopefully replicates what your current equation does in VBA with a few differences. Since I don't know the specifics of your second sheet the caching might not help at all.

If your second sheet uses the same date range for all calls to sumExpenses then it should be a bit quicker as it pre-sums everything on the first pass, If your date range changes throughout then its just doing a lot of work for nothing.

Public Cache As ObjectPublic CacheKey As StringPublic Function sumExpenses(ByVal dS As Date, ByVal dE As Date, ByVal sN As String) As VariantDim Key As StringKey = Day(dS) & "-" & Month(dS) & "-" & Year(dS) & "_" & Day(dE) & "-" & Month(dE) & "-" & Year(dE)    If CacheKey = Key Then        If Not Cache Is Nothing Then            If Cache.Exists(sN) Then                sumExpenses = Cache(sN)                Exit Function            End If            Set Cache = Nothing        End If    End If    CacheKey = Key    Set Cache = CreateObject("Scripting.Dictionary")    Dim Expenses As Worksheet    Dim Row As Integer    Dim Item As String    Set Expenses = ThisWorkbook.Worksheets("Expenses")    Row = 1    While (Not Expenses.Cells(Row, 1) = "")        If Expenses.Cells(Row, 1).Value > dS And Expenses.Cells(Row, 1).Value < dE Then            Item = Expenses.Cells(Row, 2).Value            If Cache.Exists(Item) Then                Cache(Item) = Cache(Item) + Expenses.Cells(Row, 3).Value            Else                Cache.Add Item, Expenses.Cells(Row, 3).Value            End If        End If        Row = Row + 1    Wend    If Cache.Exists(sN) Then        sumExpenses = Cache(sN)    Else        sumExpenses = CVErr(xlErrNA)    End IfEnd FunctionPublic Sub resetCache()    Set Cache = Nothing    CacheKey = ""End Sub

There could be many causes of this. I just wish Excel would tell us which one (or more) of the 'usual suspects' is committing the offence of RAM hogging at this time.

Also look for

1. Circular references

2. Fragmented Conditional formatting (caused by cutting, pasting, sorting, deleting and adding cells or rows.

3. Errors resulting in #N/A, #REF, #DIV/0! etc,

4. Over-use of the volatile functions TODAY(), NOW(), etc.

5. Too many different formats used

... in that order

While you're there, check for

6. Broken links. A formula relying on a fresh value from external data could return an error.

7. Any formulas containing #REF!. If your formulas are that messed these may well be present also. They will not cause an error flag but may cause some unreported errors. If your formulas are satisfied by an earlier condition the part of the formula containing #REF! will not be evaluated until other conditions prevail.