Forum Discussion

ribamero's avatar
ribamero
Copper Contributor
Jan 18, 2023
Solved

COUNTIF cells which contain some letters

Hi everyone! I want to use the COUNTIF function to count the cells in a range that contain any of three different letters given and in any position of the string, e.g., cells that contain the letter...
excel
Formulas and Functions
Rodrigo_'s avatar
Rodrigo_
Iron Contributor
Jan 19, 2023

Hello ribamero 

 

Try this:


Formula on B3:

=COUNTIF($A$3:$A$9,"*"&B$2&"*")
ribamero's avatar
ribamero
Copper Contributor
Jan 19, 2023

Hi Rodrigo_!
Thank you for your message.
Well, in fact, the function that I'm working on is COUNTIFS. I wanted to write an easy example with COUNTIF.
I'm going to explain my question again using the COUNTIFS function:

I want to count the cells in the range B1:B10 that begin with letters "G" or "P" and are a COLOR, range A1:A10. Something like this, though I know the criteria is wrong: COUNTIFS(B1:B10;"[G,P]*";A1:A10;"COLOR")

Regards.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jan 19, 2023

    ribamero Try this one:

    =SUM(COUNTIFS(A1:A10,"COLOR",B1:B10,{"G*","P*"}))

     

    Demonstrated in the attached file.

    • ribamero's avatar
      ribamero
      Copper Contributor
      Jan 19, 2023

      Hi Riny_van_Eekelen!
      I tried your example, and it worked very well. It was just what I was looking for.
      You have a deep knowledge of Excel that I don't have! Thank you a lot.
      Regards.

Resources