VBA Sumifs MultiSelect Item ListboxList

Question

I have a MultiSelect Listbox populated with Week values (1,2,3...52) where I can select multiple weeks. What I want is to make a Sumifs to get the number of visited retailer according to the items selected.

Ex : If I select week 27 and 28, I want the total sum of the number of visited retailer in week 27 and 28.

For now, I only have the result of one week even if I select multiple items.

Sub SumifsVisitedRetailer()

  Dim StartRow As Long
  Dim LastRow As Long
  Dim FoundColumn As String
  Dim StringToFind As String
  Dim ResultRange As Range
  Dim i as Integer, Dim x as Integer 
  Dim Measure as range, Dim retailer as range

  Dim bdd As Worksheet: Set bdd = Sheets("BDD")
  Dim cht As Worksheet: Set cht = Sheets("CHT")

  Call FoundMeasure(StartRow, LastRow, FoundColumn, StringToFind, ResultRange, Measure)
  Call FoundColRetailer(StartRow, LastRow, FoundColumn, StringToFind, ResultRange, retailer)

    LastRange = .Cells(Rows.Count, 1).End(xlUp).Row

    'Column Week
    Set wkcol = bdd.Range("D2:D" & LastRange)

      LastRow = cht.Cells(Rows.Count, 1).End(xlUp).Row

            For x = 0 To UserForm1.ListBox32.ListCount - 1

             For I = 2 To LastRow

               If UserForm1.ListBox32.Selected(x) = True Then

                 cht.Cells(I, 2) = Application.WorksheetFunction.SumIfs(Measure, retailer, 
                   cht.Cells(I, 1), wkcol, UserForm1.ListBox32.List(x)) 
               End If
            Next I
           Next x

End Sub

Answer 1

You can clear the value of the cell before adding the result of each SumIfs formula to the target cell. Something like this should work...

' Range to add SumIfs in
Dim target as Range
Set target = cht.Cells(I, 2) ' Or something, I'm not quite following your data :)
' Clear the target range's value
target.value = 0
' For each week to calculate SumIfs with
For x = 0 To UserForm1.ListBox32.ListCount - 1
     ' Add the value of the cell to the result of the SumIf function
     target.value = target.value + Application.WorksheetFunction.SumIfs( _
                                                 Measure, _
                                                 retailer, _
                                                 cht.Cells(I, 1), _
                                                 wkcol, _
                                                 UserForm1.ListBox32.List(x))
Next x