1

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

Improve this question
1
  • 1
    if your question was answered, be correct and validate the right answer
    – iDevlop
    Commented Sep 16, 2021 at 14:36

2 Answers 2

Reset to default
4

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

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

let me know if this helped

Improve this answer
2
  • 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.
    – Thufir
    Commented Mar 18, 2019 at 15:34
  • 1
    i just tried it out using madeup names and it appears to be working properly
    – Alan
    Commented Mar 18, 2019 at 15:36
3

Another option is to use SUMPRODUCT and FIND:

=SUMPRODUCT(--(ISNUMBER(FIND(C11,A1:A10))))
Improve this answer
3
  • how would that relate to regex for string data?
    – Thufir
    Commented Mar 18, 2019 at 16:35
  • 1
    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
    – cybernetic.nomad
    Commented Mar 18, 2019 at 16:40
  • ah, I found: exceljet.net/glossary/double-unary which adds to your answer. Makes more sense, and beautifully simple.
    – Thufir
    Commented Mar 18, 2019 at 16:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .