View original (French)

[Excel] COUNTIF with partial text string

Solved
lecastorlapon -  
 LGLCNfamily -
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 Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    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
      1. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453 > lecastorlapon
         
        Hello 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
      2. lecastorlapon > lecastorlapon
         
        Cool. 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
    2. LGLCNfamily
       
      I searched for hours before finding your post. Thank you!!!
      0