Search for value and then count the values in the same row as the value (1 Viewer)

[Bean Machine]

Hi All!

I have tried so many things at this point I think my brain is entirely fried. I am trying to find out how to check if a value matches a stated value in a formula and then counts the values in the same row it was found. This will be made more clear by looking at the attached image. Ideally I want to be able to check for "*"&"WKRPT"&"*" any value that has WKRPT in the name, and then count I, D, and A in the row. How could I go about doing this? I've tried HLOOKUP, IFs, COUNTIFs, MATCH etc. but to no avail. Any help would be greatly appreciated!

 

[Gasman]

I would set a range for your first column to the last row of data.
Then walk that column.
If you find your required text in the cell, then walk the columns with .Offset and inspect for each value you need then increment if found.

All in VBA though.

I just had to do something similar to enure any cancelled trips set Passengers to zero (plus a few other cells)

Sub SetCancelledPassengers()
Dim rngStatus As Range, rngCell As Range
Dim iLastRow As Integer

iLastRow = GetLastRow(ActiveSheet.Name) '("SF660EK")
Set rngStatus = Range("E2:E" & iLastRow)
For Each rngCell In rngStatus
    If Left(rngCell.Value, 9) = "Cancelled" Then
        'Debug.Print rngCell.Address & "-" & rngCell.Value & "  " & rngCell.Offset(0, -2).Address
        rngCell.Offset(0, -2).Value = 0
        rngCell.Offset(0, 2).Value = 0
        rngCell.Offset(0, 5).Value = 0
        If rngCell.Value = "Cancelled A" Then
            rngCell.Offset(0, 5).Value = rngCell.Offset(0, 3).Value
        End If
    End If
Next

Set rngStatus = Nothing
Set rngCell = Nothing
 
End Sub