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
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jan 18, 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 18, 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 18, 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.