Count occurrences of values in named range

[crookesa]

I have a list of values in a named range and I want to count each of their occurrences in another range with text added to the end of it.
For example, the named range contains the value "L, E, & N". I want to find the amount of times those values appear in another range with "-FAL" or "-SAL" appended to them.
e.g L-FAL, E-SAL, N-FAL.

Is it possible to do this without creating a UDF?

 

[Misca]

I'd solve something like this with the Power Query. Not an UDF solution but not exactly a formula solution either.

However you didn't say how do you want to count the values: If one of the text rows in your search range includes one or more instances of one or more search combinations do you want it count as one or do you want to count the number of actual instances? If a text contains the text L-FAL twice and E-SAL once, do you want to count that as 1, 2 or 3? And do you want the matches to be case sensitive or not?

 

[crookesa]

I'd solve something like this with the Power Query. Not an UDF solution but not exactly a formula solution either.

However you didn't say how do you want to count the values: If one of the text rows in your search range includes one or more instances of one or more search combinations do you want it count as one or do you want to count the number of actual instances? If a text contains the text L-FAL twice and E-SAL once, do you want to count that as 1, 2 or 3? And do you want the matches to be case sensitive or not?

So the L, E, N refers to different working duties can be assigned to an employee. If they they take a 1st half annual leave (FAL) or a 2nd half annual leave (SAL) the duty is appended with either -FAL or -SAL so reflect their what they are working. I have a grid of names in the rows and days of the months as columns. I want to summarise below each column the amount of times each one appears in the corresponding day (column) above. I would also like to use the wildcard functionality above after the duty code to catch all and search it as case insensitive.
Below is the UDF I have tried to write as a solution so far.

Function count_operational_half_days(half_al_type As Integer, duties_range As range, search_range As range) As Integer

Dim total As Integer
total = 0

For Each cell In duties_range
    For Each search_cell In search_range
        Select Case half_al_type
        Case 1 'First Half AL
            If UCase(search_cell.Value) & "" = UCase(cell.Value) & "-FAL" Then
                total = total + 1
            ElseIf UCase(search_cell.Value) & "" = UCase(cell.Value) & "-OJT-FAL" Then 'OJT and FAL
                total = total + 1
            ElseIf UCase(search_cell.Value) & "" = UCase(cell.Value) & "~*-FAL~*" Then 'Catch all with FAL
                total = total + 1
            End If
        Case 2 ' Second Half AL
            If UCase(search_cell.Value) & "" = UCase(cell.Value) & "-SAL" Then
                total = total + 1
            ElseIf UCase(search_cell.Value) & "" = UCase(cell.Value) & "-OJT-SAL" Then 'OJT and SAL
                total = total + 1
            ElseIf UCase(search_cell.Value) & "" = UCase(cell.Value) & "~*-SAL~*" Then 'Catch all with SAL
                total = total + 1
            End If
        End Select
    Next search_cell
Next cell
count_operational_half_days = total
End Function