How to Sum, Filtered Rows based on drop down selection in Excel using VBA?

Question

I have an Excel sheet(Data) having columns Name, Region, Payment Type and Amount.
Data

I created a 'Data Validation' drop down list of Regions in another sheet(Output).
Output

When I select a particular Region from the drop down list, total Amount for that Region should be displayed in the next cell.

I wrote VBA code, but the cell doesn't display the total Amount when I select the Region from the list.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngRegion As Range
    Dim rngData As Range
    Dim cell As Range
    Dim selectedRegion As String
    Dim totalAmount As Double
    Dim outputCell As Range
    
    ' Define the range for the region dropdown list in Sheet2
    Set rngRegion = Sheets("Output").Range("A1:B2") ' Modify the range as needed
    
    ' Define the range for the data in Sheet1
    Set rngData = Sheets("Data").Range("A2:D300") ' Modify the range as needed
    
    ' Define the output cell for total amount in Sheet2
    Set outputCell = Sheets("Output").Range("B2") ' Modify the cell as needed
    
    ' Check if the changed cell is within the region dropdown list in Sheet2
    If Not Intersect(Target, rngRegion) Is Nothing Then
        ' Get the selected region
        selectedRegion = Target.Value
        
        ' Reset total amount
        totalAmount = 0
        
        ' Loop through each row in the data range in Sheet1
        For Each cell In rngData
            ' Check if the region matches the selected region
            If cell.Offset(0, 1).Value = selectedRegion Then
                ' Add the amount to the total
                totalAmount = totalAmount + cell.Offset(0, 3).Value
            End If
        Next cell
        
        ' Display the total amount in the output cell in Sheet2
        outputCell.Value = totalAmount
    End If
End Sub

Answer 1

Your present code recursively call your sub because when you set the result to the cell it again invokes the sub.

Two ways to correct the issue

1. Define only the dropdown as the rngRegion

Set rngRegion = Sheets("Output").Range("A2") ' Modify the range as needed

2. Apply the EnableEvents property like this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngRegion As Range
    Dim rngData As Range
    Dim cell As Range
    Dim selectedRegion As String
    Dim totalAmount As Double
    Dim outputCell As Range
    
    ' Define the range for the region dropdown list in Sheet2
    Set rngRegion = Sheets("Output").Range("A1:B2") ' Modify the range as needed
    
    ' Define the range for the data in Sheet1
    Set rngData = Sheets("Data").Range("A2:D300") ' Modify the range as needed
    
    ' Define the output cell for total amount in Sheet2
    Set outputCell = Sheets("Output").Range("B2") ' Modify the cell as needed

    Application.EnableEvents = False    'added
    
    ' Check if the changed cell is within the region dropdown list in Sheet2
    If Not Intersect(Target, rngRegion) Is Nothing Then
        ' Get the selected region
        selectedRegion = Target.Value
        
        ' Reset total amount
        totalAmount = 0
        
        ' Loop through each row in the data range in Sheet1
        For Each cell In rngData
            ' Check if the region matches the selected region
            If cell.Offset(0, 1).Value = selectedRegion Then
                ' Add the amount to the total
                totalAmount = totalAmount + cell.Offset(0, 3).Value
            End If
        Next cell
        
        ' Display the total amount in the output cell in Sheet2
        outputCell.Value = totalAmount
    End If
    Application.EnableEvents = True   'added
End Sub