QP School
Sum values based on criteria - Printable Version

+- QP School (https://qomplainerzschool.lima-city.de)
+-- Forum: Tutorials (https://qomplainerzschool.lima-city.de/forumdisplay.php?fid=3)
+--- Forum: Excel VBA Tutorials (https://qomplainerzschool.lima-city.de/forumdisplay.php?fid=48)
+--- Thread: Sum values based on criteria (/showthread.php?tid=5268)



Sum values based on criteria - Qomplainerz - 07-27-2023

Example:

Sub SumValuesBasedOnCriteria()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim sumResult As Double
    Dim targetCriteria As String

    'Set the worksheet where the data is located
    Set ws = ThisWorkbook.Sheets("Sheet1")

    'Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    'Specify the target criterion in column A
    targetCriteria = "Category1"

    sumResult = 0 'Initialize the sum

    'Loop through each row and calculate the sum of values in column B
    For i = 1 To lastRow
        If ws.Cells(i, "A").Value = targetCriteria Then
            sumResult = sumResult + ws.Cells(i, "B").Value
        End If
    Next i

    MsgBox "Sum of values for " & targetCriteria & ": " & sumResult
End Sub

Explanation:

The code defines a subroutine named SumValuesBasedOnCriteria.
It declares variables for the worksheet (ws), the last row with data (lastRow), a loop counter (i), the sum result (sumResult), and the target criterion (targetCriteria) to calculate the sum for.
Set ws = ThisWorkbook.Sheets("Sheet1") specifies the worksheet ("Sheet1") where the data is located.
The lastRow variable is determined using ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, which finds the last row with data in column A.
targetCriteria = "Category1" specifies the criterion (Category1) for which the sum will be calculated.
The For loop iterates through each row from 1 to lastRow.
The If statement checks if the value in column A (ws.Cells(i, "A").Value) matches the targetCriteria. 
If it does, the value in column B (ws.Cells(i, "B").Value) is added to the sumResult.
Finally, a message box displays the sum of values for the specified criterion.