Simple pattern with Excel COUNTIF to match a substring?

Question

How do I fix this formula: =COUNTIF(A1:A10,"*jim*") to match anything containing the sub-string of "jim" but from a cell, as: =COUNTIF(A1:A10,C11) where cell C11 contains the text "jim"?

Perhaps another cell to wrap "jim" with asterisks?

(Surely I don't need to define a special function, or resort to Visual Basic?)

see also:

how to pass a string value into COUNTIF?

https://stackoverflow.com/q/22542834/262852

Solution 1

concat the regex string and the cell string within the formula as such:

=COUNTIF(A1:A10,"*"&C11&"*")

let me know if this helped

Solution 2

Another option is to use SUMPRODUCT and FIND:

=SUMPRODUCT(--(ISNUMBER(FIND(C11,A1:A10))))

Comments

[iDevlop]

if your question was answered, be correct and validate the right answer

[Thufir]

that looks right, I won't be able to check for at least a day. I think that's at least towards a solution, if not the actual solution. Thanks.

[Alan]

i just tried it out using madeup names and it appears to be working properly

[Thufir]

how would that relate to regex for string data?

[cybernetic.nomad]

No need for regex, this function looks for the presence of whatever string is in cell C11 we don't care where it is or if there's more text around it, only that it's present. If it's present, ISNUMBER returns "True" (1), if not, it returns "False" (0). The SUMPRODUCT then adds all the 1s, resulting in a count of all the cells that include the desired string

[Thufir]

ah, I found: exceljet.net/glossary/double-unary which adds to your answer. Makes more sense, and beautifully simple.