How to count cells with certain text in Excel


In my worksheet I have a lot of cells filled with different data, but some of them have similar values in it. How can I count the cells with particular text?



Selected Answer


If you had data in cells A1:A6 say and were looking for "word", you use COUNTIF and put the *  wildcard either side of that criteria, so

would return a count for word anywhere in a cell (note that COUNTIF is case insensitive so would count if the cell included WoRD e.g.)

In my example above, it would count also a cell containing "foreword" too, but you could put a space after the first wildcard like this:

=COUNTIF(A1:A6,"* word*")
to count only cells with "word".

To get only cells ending in " word",use:

=COUNTIF(A1:A6,"* word")

Hope you can use this.



Hi John,
I don't understand how when looking for "word" it would count "forward". Maybe change your example to use "buzzword" instead of "forward"
WillieD24 (rep: 90) Sep 25, '21 at 12:52 pm
Thanks Willie, my mistake! I've changed the example to "foreword" instead
John_Ru (rep: 2147) Sep 25, '21 at 12:56 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login