Count cells that end with "...-ABC"

[WildWill]

Hi

I have a data range (A1:D10) with data which contains any random text prefix but which will always end with a "-" followed by 3 Alphanumeric characters, e.g.:

Andy-DRA
Rob-M&R
Peter-MUR
Andrew-DRA
Will-DRA

I want to count all cells which end with a specific criteria, e.g. If In cell E1, I enter "DRA", then the answer in F1 should be "3". What type of formula can I use?

Thanks!

 

[syntaxed]

This code will always keep the search limited to cells in A1:D10.

Function CountCombos(SearchStr As String)
    For RowCount = 1 To 10
        For ColCount = 1 To 4
            If Right(Cells(RowCount, ColCount), 3) = SearchStr Then
                CurrCount = CurrCount + 1
            End If
        Next
    Next
    CountCombos = CurrCount
End Function

The function may be entered into a new module and called from any cell like this:

=CountCombos(E1)

 

[Jon von der Heyden]

Try

=COUNTIF(A1:D10,"*"&E1)

 

[Weaver]

Just in case there are any names ending in DRA (like Sandra, maybe?), I'd use

=COUNTIF(A1:D10,"*-"&E1)

 

[WildWill]

Thanks Guys!!! Sorted.

 

[Dryver14]

Ive just tried both formulas above

could someone explain the "*" to me please

BTW "*_" only returned the 1 with the _ in it

 

[Jon von der Heyden]

It's a wildcard character, so:

*xyz* This will look for strings that contain xyz
*xyz This will look for strings that end with xyz
xyz* This will look for strings that start with xyz