Summarize Data with SumIf with Excel VBA

What is SUMIF?

The SUMIF function in Excel allows you to sum values in a range based on a given condition. It checks each value against the criteria and sums the corresponding values that meet the condition.

The syntax for SUMIF in Excel is:

=SUMIF(range, criteria, [sum_range])

• range: The range of cells to evaluate against the criteria.
• criteria: The condition that must be met for a cell to be included in the sum.
• sum_range (optional): The actual range to sum if it’s different from the range you’re evaluating.

For example:

=SUMIF(A1:A10, « Apple », B1:B10)

This formula sums the values in cells B1:B10 where the corresponding values in A1:A10 are equal to « Apple. »

Using SUMIF in VBA

In VBA, you can automate the SUMIF function with the WorksheetFunction object. Here’s a detailed example of how to implement it:

Excel VBA Code to Summarize Data Using SUMIF

Step-by-Step Code:

Sub SummarizeDataWithSUMIF()
    ' Declare the necessary variables
    Dim ws As Worksheet
    Dim sumRange As Range
    Dim criteriaRange As Range
    Dim criteria As String
    Dim result As Double
    Dim outputCell As Range
    ' Set the worksheet to work with (can be adjusted to your specific sheet)
    Set ws = ThisWorkbook.Sheets("Sheet1")  
    ' Define the range to sum and the range that will be evaluated
    Set criteriaRange = ws.Range("A2:A10")  ' Range with criteria (e.g., categories)
    Set sumRange = ws.Range("B2:B10")       ' Range with values to sum
    ' Define the criteria (you can change this to match your needs)
    criteria = "Apple"  ' For example, we want to sum all values in Column B where Column A is "Apple"  
    ' Use the WorksheetFunction to apply SUMIF
    result = Application.WorksheetFunction.SumIf(criteriaRange, criteria, sumRange)
    ' Define the output cell (where the result will be displayed)
    Set outputCell = ws.Range("D2")
    ' Output the result to the defined cell
    outputCell.Value = result
    ' Optional: Display a message box to confirm the action
    MsgBox "The total sum for criteria '" & criteria & "' is: " & result, vbInformation, "SUMIF Calculation"
End Sub

Explanation of the Code:

1. Declare Variables:
   • ws is a variable that represents the worksheet we are working with.
   • sumRange and criteriaRange represent the ranges of cells to sum and the cells to evaluate against the criteria.
   • criteria is the condition that needs to be met (in this case, « Apple »).
   • result stores the result of the SUMIF calculation.
   • outputCell represents where the result will be placed in the worksheet.
2. Setting Worksheet and Ranges:
   • The ws variable is set to a specific worksheet (in this case, « Sheet1 »). You can change « Sheet1 » to the actual sheet name.
   • criteriaRange is defined as the range of cells that contain the data you want to check against (e.g., A2:A10).
   • sumRange is defined as the range of cells that you want to sum (e.g., B2:B10).
3. Setting the Criteria:
   • The criteria is set to « Apple », meaning the code will sum values in B2:B10 where the corresponding value in A2:A10 is « Apple. »
4. Using SUMIF:
   • Application.WorksheetFunction.SumIf(criteriaRange, criteria, sumRange) performs the summing operation.
     • The function checks each value in criteriaRange (A2:A10) to see if it meets the specified criteria (in this case, « Apple »).
     • If the value meets the criteria, it sums the corresponding value in sumRange (B2:B10).
5. Output the Result:
   • The sum result is placed into cell D2 in the worksheet (you can adjust this as necessary).
6. Optional Message Box:
   • A message box is shown to the user that confirms the calculation result.

How to Use the Code:

1. Open Excel and press Alt + F11 to open the VBA editor.
2. Insert a new module by clicking Insert > Module.
3. Copy and paste the VBA code into the module.
4. Close the VBA editor.
5. Go back to your Excel sheet and press Alt + F8 to run the macro SummarizeDataWithSUMIF.

The code will sum the values in column B where the corresponding values in column A are « Apple » and display the result in cell D2.

Conclusion:

This VBA code helps automate the use of the SUMIF function in Excel by allowing you to quickly summarize data based on a condition. By changing the criteria and adjusting the ranges, you can tailor this solution to fit various scenarios for summarizing data.