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?

• It is much easier to change SUMIFS than the complicated MATCH/INDEX functions.
• I think I could introduce an error in the MATCH/INDEX functions more easily.

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.