SOLVED

New Contributor

# 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 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!

4 Replies

# Re: COUNTIF cells which contain some letters

Hello @ribamero

Try this:

Formula on B3:

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

# Re: COUNTIF cells which contain some letters

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

best response confirmed by ribamero (New Contributor)
Solution

# Re: COUNTIF cells which contain some letters

@ribamero Try this one:

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

Demonstrated in the attached file.

# Re: COUNTIF cells which contain some letters

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.