+- 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.