Counting text strings with variations

Copper Contributor

I would like to count the occurrence of the text string "ama" within a column, e.g. 

=COUNTIF(A1:A6000),"*ama*")

 

However, I would also like to count other variations: amas, ama's, AMA, AMAs, AMA's

without capturing text like "Amanda" that features the string "ama". 

 

Appreciate advice on how to approach this!

3 Replies

without capturing text like "Amanda" that features the string "ama". on my side it works

 

 

Hi there. Thanks for your help, but not sure that I understand. I mean that I would like to count all variations as a standalone string (ama, amas, ama's, AMA, AMAs, AMA's) but not any instances where the string appears within another word such as Amanda.
please,
Attach a sample excel file to your post. Show input and desired output.