View original (French)

[Excel] COUNTIF with partial text string

Solved
lecastorlapon -  
Hello,

I am trying to count the number of cells that contain a certain text that is only a part of the value of the cell. Example:
Cell 1: Marc is handsome
Cell 2: Marc is rich
Cell 3: Sophie is beautiful

I want a formula that counts the number of cells where "Marc" appears, which is 2 here. I think I need to tweak the COUNTIF function, but I don't know how.

Thank you for your help

Configuration: Windows 7 / Firefox 8.0

1 answer

  1.  Vaucluse Contributor
    Good evening 
    =COUNTIF(A1:A100,"*Marc*")

    or 
    =COUNTIF(A1:A100,"*"&B1&"*")

    by placing the searched text in B1
    Regards
    111
    1. lecastorlapon
      It seems to be working, great! I didn't know about this thing with asterisks. Does it work in all Excel formulas?
      Anyway, thanks for the tip.
      0
    2. Vaucluse Contributor
      @lecastorlaponHello lacastorlapon(!!?)

      No, this does not apply, for example, with formulas that use a mathematical operator such as +, - > < as a field separator

      but only via the semicolon
      for example:
      =IF(A1="*A*"... does not work

      however:
      VLOOKUP("*A*";Field... works. as well as MATCH("*"&B1&"*";Field)
      the same with SUMIF("*"&B1&"*";.....
      Etc...

      additionally and for your information
      if you write
      "*"&B1 or "*text", the formula only searches for the first word of the text
      with
      B1&"*" or "text*", with the last word surrounded by two asterisks, the code searches anywhere in the text.

      Safe travels

      Best regards
      4
    3. lecastorlapon
      @lecastorlaponCool. In fact, it's like a meta character, I use it when I do Windows searches.

      Otherwise, for mono pseudo, it comes from my nickname (Castor) and a sketch by Les Nuls about Lapp beavers.

      See you!
      0
    4. LGLCNfamily
      I searched for hours before finding your post. Thank you!!!
      0