SUMIFS vs MATCH & INDEX

I have one Time Card template setup with SUMIFS (used for Excel 2007 and above templates) and one set up with MATCH/INDEX (used for my 2003 and below templates). I tested the calculation speeds for each in Excel 2013 (preview) on Windows 8.

Here’s the VBA test code:

Sub test()

    Dim dTimer As Double
    Dim iLoops As Integer, iTotalIterations As Integer
    Dim wksStage As Worksheet

    Set wksStage = ThisWorkbook.Worksheets("Staging Area")
    iTotalIterations = 1
    dTimer = Timer()

    For iLoops = 0 To iTotalIterations
        wksStage.Calculate
    Next iLoops

    Debug.Print Timer() - dTimer

End Sub

And here’s the results (seconds):


Iterations SUMIFS MATCH/INDEX


1 50 0.20 3.07 0.09 1.30


So where do I go from here? I think I will keep the two separate templates for now, even though I could go to one. The reasons?

I will post the 2003 template for anyone that wishes faster calculation times, but it is pretty fast now, so I don’t know if that would be a huge issue.

Thu Mar 14, 2013 | tags: excel excel functions time card vba

comments powered by Disqus