CODEWITHSUNDEEP
vbaexcel
David
David

Reputation: 16028

CountIfs horribly slow in Excel

Scenario:

I have a scenario where each account can have many customers, and each customer can have many accounts (many to many).

I am creating a report to count how many accounts each customer has.

enter image description here

To do this, I am using this formula:

=COUNTIF(CustAccount[Customer ID], [@[Customer ID]])

This works fine. The problem is that the CustAccount table contains 65K rows. The report table contains 45K Customer IDs. It is taking more than 20 minutes to perform this calculation on a relatively slow PC.

I am using the obvious optimizations such as:

 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual

Is it looping all of the 65K rows each time to acquire a count for every row in the 45K table? Any ideas on what can be done to significantly improve performance?

Upvotes: 1

Views: 2789

Answers (2)

Valdis Grinbergs
Valdis Grinbergs

Reputation: 453

Another way to do it is to accumulate the count of accounts by CustomerID in a Dictionary object, then write out the dictionary and sort it by CustomerID. See example below:

Sub CountAccountsByCustomerID()
    ' Need to add reference in Menu > Tools > References
    ' turn checkbox on for "Microsoft Scripting Runtime" to use dictionary
    Dim customers As Dictionary
    Set customers = New Dictionary
    Dim AccountTable As Range
    Set AccountTable = Sheets("Sheet1").Range("A2")
    Dim offset As Long
    offset = 0
    Dim OutputTable As Range
    Set OutputTable = Sheets("Sheet1").Range("D2")
    Dim customer As String
    Dim item As Variant

    ' Build dictionary of counts of accounts for all CustomerIDs
    Do While AccountTable.offset(offset, 0) <> ""
        customer = AccountTable.offset(offset, 1).Value
        If customers.Exists(customer) Then
            customers(customer) = customers(customer) + 1
        Else
            customers(customer) = 1
        End If
        offset = offset + 1
    Loop

    'Write table of customerIDs and counts of accounts
    offset = 0
    For Each item In customers.Keys
        OutputTable.offset(offset, 0).Value = Str(item)
        OutputTable.offset(offset, 1).Value = customers.item(item)
        offset = offset + 1
    Next

    'Sort OutputTable by CustomerID (assumes column headings in row above range OutputTable
    OutputTable.CurrentRegion.Sort OutputTable, xlAscending, , , , , , xlYes

End Sub

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166156

How you can run a SQL query on data in Excel:

Sub SQLTester()
'Add a reference to "Microsoft ActiveX data objects"
'Workbook must have been saved to run this

Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
Dim wb As Workbook

    Set wb = ThisWorkbook

    wb.Save

    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & wb.FullName & _
               ";Extended Properties='Excel 8.0;HDR=Yes'"

    oRS.Open " select Customer, count(Account) " & _
             " from [Data test$A1:B2201] group by Customer", oConn

    wb.Sheets("Data test").Range("E2").CopyFromRecordset oRS

    oRS.Close

End Sub

Upvotes: 4

Related Questions