COUNTIF cells which contain some letters

[ribamero]

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 letters "s" or "a" or "f". Some examples are:

1. sex

2. air

3. fuel

4. fast

5. ants

6. stuff

Can I do that? Thank you!

[#2]

Hello @ribamero 

Try this:

Formula on B3:

=COUNTIF($A$3:$A$9,"*"&B$2&"*")

[#3]

Hi @rrestrivera!
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.

[#4 · Accepted solution]

@ribamero Try this one:

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

Demonstrated in the attached file.

[#5]

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.