Excel case-sensitive COUNTIF with wildcards: native function?

Question

I'm trying to find a native Excel function (or combination thereof) which will behave precisely in the manner of COUNTIF (i.e will handle wildcards), but is case-sensitive.

I have successfully used the SUMPRODUCT/EXACT functions, which do indeed perform the case-sensitive count: however the problem is that I can't get these to recognise wildcards.

Perhaps it's better to give an example of what I actually need. I want to take a range, and search for the number of occurrences of the text "TBA", where the letters must be in capitals, but they can appear anywhere in the cell. For example:

TBA - should count

tbA - should not count

somethingTBAsomething - should count

somethingtBasomething - should not count

=COUNTIF(A1:A10,"*TBA*")
' Correctly accounts for wildcards (*), but isn't case-sensitive

=SUMPRODUCT(--EXACT(A1:A10,"TBA"))
' Is case-sensitive, but only finds whole cell values which match

=SUMPRODUCT(--EXACT(A1:A10,"*TBA*"))
' Doesn't recognise * as a wildcard, because it's
' literally only searching for cells with asterisks
' either side of the letters

To solve my problem I have written a user-defined function as follows:

Option Compare Binary

' Case-sensitive COUNTIF
Function CS_Countif(rng As Range, str As String) As Long
    Dim Matches As Long, cl As Range
    For Each cl In rng
        If InStr(cl.Value, str) > 0 Then Matches = Matches + 1
    Next cl
    CS_Countif = Matches
End Function

However, this function slows down the spreadsheet calculation, and there is a notable delay every time one of the cells in the relevant range is updated.

Can anyone figure out a combination of native Excel functions (in the same manner as SUMPRODUCT/EXACT) which will do what I'm looking for? Thanks.

Rory Rory · Accepted Answer · 2016-06-14T10:07:27

Since FIND is case sensitive, you can use that:

=COUNT(INDEX(FIND("TBA",A1:A7),))

The INDEX function is only there so you don't have to array-enter the formula.

Excel case-sensitive COUNTIF with wildcards: native function?

1 Answers