VBA SumIf cell contains text-CodePudding

I am trying to get the total qty if the cell contains specific text to another cell but the total I am getting at the moment is 0.

Please see my script below:

Sub SUMIF()

Dim S3 As Range
Dim S3T As Range

Set S = Range("A3:A500")
Set QTY = Range("D3:D500")

Range("I3") = _
WorksheetFunction.SUMIF(S, "S3", QTY)
Range("I4") = _
WorksheetFunction.SUMIF(S, "S4", QTY)
Range("I5") = _
WorksheetFunction.SUMIF(S, "S5", QTY)
Range("I6") = _
WorksheetFunction.SUMIF(S, "S6", QTY)


End Sub

CodePudding user response：

Try other method. Like for example InStr:

Sub SUMIF()

'Find the last row of each column with data
Dim Fi, Fi2 as integer
Dim Exa as string
Dim Can as integer
Fi = Cells(Rows.Count, 8).End(xlUp).Row
Fi2 = Cells(Rows.Count, 1).End(xlUp).Row

'Let's create a double loop
For i = 3 to Fi
  Exa = cells(i, 8)
  For j = 3 to Fi2
    'This method gives a integer if it finds in the string of the first argument with the position of this string
    If InStr(cells(j, 1), Exa) > 0 then
      Can = cells(j, 4)
      Cells(i, 9) = Cells(i, 9)   Can
    End if
  Next j
Next i

End Sub

CodePudding user response：

"Simplicity is the ultimate sophistication"

Sub sumif()
    
    Dim s As Range
    Set s = Range("a3:a100")
    
    Dim qty As Range
    Set qty = Range("d3:d100")
    
    Range("I3") = WorksheetFunction.sumif(s, "*" & "S3" & "*", qty)
    Range("I4") = WorksheetFunction.sumif(s, "*" & "S4" & "*", qty)
    Range("I5") = WorksheetFunction.sumif(s, "*" & "S5" & "*", qty)
    Range("I6") = WorksheetFunction.sumif(s, "*" & "S6" & "*", qty)
    
    End Sub