[Excel VBA] How to Sum Data Based on Conditions Using SUMIF

Summing data that matches specific conditions, such as “calculating total sales for the Tokyo branch from a sales list,” is a fundamental task in Excel work.

When trying to do this in VBA, you might first think of using a For loop and an If statement to check each row one by one. However, as the amount of data increases, this method becomes extremely slow.

A smarter and faster solution is to call the worksheet function SUMIF directly from VBA. In this article, I will compare two methods and explain the huge advantages of using WorksheetFunction.SumIf.

Method 1: Checking Row by Row with a Loop (Not Recommended)

First, let’s look at code that performs a conditional sum using basic VBA commands.

Sub ConditionalSumWithLoop()
    Dim i As Long
    Dim totalSales As Double
    Dim targetSheet As Worksheet
    
    Set targetSheet = ThisWorkbook.Worksheets("SalesData")
    
    ' Check 100,000 rows one by one
    For i = 2 To 100001
        If targetSheet.Cells(i, "C").Value = "Tokyo" Then
            totalSales = totalSales + targetSheet.Cells(i, "E").Value
        End If
    Next i
    
    ThisWorkbook.Worksheets("Summary").Range("B2").Value = totalSales
    MsgBox "Loop process completed."
End Sub

The logic of this code is easy to understand. However, because it checks 100,000 rows one by one, it can take several seconds to tens of seconds to run.

Method 2: Using WorksheetFunction.SumIf (Highly Recommended)

Next is the method using WorksheetFunction.SumIf, which utilizes Excel’s high-speed calculation engine.

Code and Explanation

Sub ConditionalSumWithSumIf()
    ' Declare variables
    Dim criteriaRange As Range
    Dim sumRange As Range
    Dim condition As String
    Dim totalSales As Double
    
    '--- Settings ---
    ' Column to search for condition (Col C: Branch Name)
    Set criteriaRange = ThisWorkbook.Worksheets("SalesData").Range("C:C")
    ' Column containing numbers to sum (Col E: Sales Amount)
    Set sumRange = ThisWorkbook.Worksheets("SalesData").Range("E:E")
    ' Condition to sum
    condition = "Tokyo"
    '--- Settings End ---
    
    ' Sum values matching the condition at once using WorksheetFunction.SumIf
    totalSales = WorksheetFunction.SumIf(criteriaRange, condition, sumRange)
    
    ' Output result to cell
    ThisWorkbook.Worksheets("Summary").Range("B2").Value = totalSales
    
    MsgBox "The total sales for " & condition & " is " & totalSales
End Sub

This code completes instantly, even if there are hundreds of thousands of rows.

WorksheetFunction.SumIf takes exactly the same arguments as the SUMIF function used in Excel cells.

WorksheetFunction.SumIf(Range, Criteria, [Sum_range])

• Arg 1 (Range): The range of cells to evaluate (e.g., Branch Name column).
• Arg 2 (Criteria): The condition to sum (e.g., “Tokyo”).
• Arg 3 ([Sum_range]): The actual cells to sum (e.g., Sales Amount column).

Summary

When summing data that matches a condition, there is a clear difference between the two methods:

Conclusion

When performing conditional sums or counts, you should use the WorksheetFunction object (such as SumIf, CountIf, or SumIfs) without hesitation.

Using Excel’s built-in optimized functions is far faster, makes the code simpler, and results in fewer bugs than writing complex loops yourself. This is a very important concept for efficient VBA programming.