CODEWITHSUNDEEP
stringvbaexcelsum
Nicholas Kan
Nicholas Kan

Reputation: 161

Sumif a cell contains

My problem is the sumif part, it could not search all cells which contains the string 'model' and add up the value pertaining to 'model'.

Please help to check if there is any error when I am using the sumif function.

Sub Test()
    Dim file As String
    Dim file1 As String
    Dim wb As Workbook
    Dim wb1 As Workbook
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim i As Variant
    Dim y As Variant
    Dim LastRow As Long
    Dim LastRow2 As Long
    Dim Model As String
    Dim NextMonth As String
    Dim SumUp As Double

    For i = 3 To LastRow
        For y = 4 To LastRow2

            If InStr(1, ws2.Cells(i, 3).Value, "GOOD FORTUNE CO LTD") And InStr(1, ws2.Cells(i, 10).Value, "FORTUNE ASIA") And InStr(10, ws2.Cells(i, 7).Value, "K") <> 10 Then       'Change Location
                Model = Right(Left(ws2.Cells(i, 7), 9), 6)

                If InStr(1, ws1.Cells(y, 5), Model) And ws1.Cells(y, 21) <> 0 Then   'Change Column Number of 2nd Condition
                    SumUp = Application.WorksheetFunction.SumIf(ws1.Range(ws1.Cells(4, 5), ws1.Cells(y, 5)), InStr(1, ws1.Cells(y, 5), Model), ws1.Range(ws1.Cells(4, 21), ws1.Cells(y, 21)))
                    ws2.Cells(i, 18).Value = SumUp        'Change Column Number of right hand side
                End If
            End If
        Next y
    Next i

    MsgBox "Completed!"
End Sub

Upvotes: 0

Views: 1107

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Hard to be sure without examples of your original data and desired output, but it appears that your criteria argument is incorrect.

As written:

InStr(1, ws1.Cells(y, 5), Model)

This will return a number; not a string.

Also, if you want the criteria argument to be that the cell(s) contain the value in Model, then you will also need wild cards.

Perhaps something like:

"*" & Model & "*"

Upvotes: 0

Rory
Rory

Reputation: 34045

You need wildcards if you want to check for cells containing the text:

SumUp = Application.WorksheetFunction.SumIf(ws1.Range(ws1.Cells(4, 5), ws1.Cells(y, 5)), "*" & Model & "*", ws1.Range(ws1.Cells(4, 21), ws1.Cells(y, 21)))

Upvotes: 1

Related Questions