If you’re using Google Sheets and you want to count the occurrences of a certain letter or word, there’s a pretty easy function that you can use.
Looking for a word and only that word
In this first set of data in the image above and also in this linked spreadsheet, we will be counting any cells that contain the word “Yellow” and only that word. To count the occurrences of the word yellow in the range B2 to B9 you can use the count COUNTIF function as such: =COUNTIF(B2:B9,"yellow"). It performs a conditional count. In this case, only if the cell or ranges of cells is equal to Yellow. Yellow is not case-sensitive so this is going to pick up three instances even though B7 is not capitalized. If the COUNTIF technique is doing everything you need, then you’re done and there is no need to try anything more involved.
Looking for a word occurring anywhere a cell
Looking at this second set of data, things will get a little bit more complicated. We are looking for a certain word that occurs anywhere in any of these cells. First, you want to use COUNTIF again and give it a range =COUNTIF(C2:C9, "*Yellow*"). For this example, the range will be C2 to C9. If it has the word yellow and anything before which is what the asterisk means, and anything after it which is the second asterisk, then it should be counted. It just has to have yellow in some part of it. Anything can be nothing so it can start or end with yellow too. This function is also counting 3 because of the yellow plane, the yellow car, and the little yellow boat.
Case-Sensitivity
The COUNTIF function is not case-sensitive. To count cells with specific capitalization, follow the second example in this tutorial.
Using a plugin
As an alternative to the options above, you may want a plugin to do the heavy lifting for you. I like to use a plugin called Power Tools. This will give you a menu option with, among other things, advanced Find and Replace features. This will give you a list of all the occurrences of a word in your spreadsheet, but it won’t give you a count of them. Depending on the size of the spreadsheet, this may be the preferable option.
I hope that was helpful and now you know the formula for counting any occurrences of any word that you’re looking for.
tiffany says:
How do you do this for the same work in multiple sheets (or tabs) within a sheet?
Say, sheet 1, sheet 2 and sheet 3 all have the same list of items in the same columns in each sheet. How would I gather a count of that number?
admin says:
String a set of COUNT functions together that reference each cell. You reference other cells like this video https://youtu.be/zs3ku4uVoho?t=794
Tina Forshey says:
How do you go one step more and add find all the February’s and then count function?
Right now I have:
=COUNTIF(‘Form Responses”!K2:K900,”Average”)
But I want to find February’s in Form Responses E2:E900
Prolific Oaktree says:
=COUNTIF(‘Form Responses’!E2:E900,”February”)
Alaqmaar Mustafa Gandhi says:
Hey, is it possible to use a similar function if you want to use referencing, like Lets say if I want =countif(A:A, B2), etc?
Prolific Oaktree says:
Yes, you could make that work.
Ben says:
in alaqmaar’s example, it will only catch the cells containing exactly what’s in b2. how do we capture what contains b2 without having to put in the text
Prolific Oaktree says:
Can you give an example of what you’re asking? I’m not sure what you mean.
jack smith says:
it would match only yellow to yellow and not yellow boat
Dakota Whaley says:
Hi Ben, thank you for asking this.
I ran into the same problem, and found the solution here: https://www.myonlinetraininghub.com/excel-wildcards-in-your-sumif-countif-and-vlookup
Example: =COUNTIF(A:A,”*”&B2&”*”)
Todd Allis says:
Thanks, that’s exactly what I needed!
kmc says:
How do you do this if one word contains another word in it?
For example, say I want to count instances of both “Alex” and “Alexandra”. How do I format it so that all instances of “Alexandra” don’t get counted as instances of “Alex”?
Harmen says:
What you could do is =countif(A:A;”*Alex*”)-countif(A:A;”*Alexandra*”)
That way every Alex that is counted but is Alexandra is subtracted and you leave only Alex
Zoe says:
Thanks for all the information. I have been trying to use this formula, but for some reason its not working for me. I would love for the formula to count how many times do I have the letter “P” in the column.
Jack Smith says:
if you have list with some things repeated with number in the adjacent row and you want to add the total for example
a 4
b 3
a 2
c 7
b 1
and you want to get the total for a (6) for b (4) and for c (7)
Prolific Oaktree says:
I would use SUMIF for that.
jack smith says:
it would only match yellow to yellow and not yellow boat
Uri says:
1. What if i want to count the instances of the combination where column B shows “yellow” and column C shows anything more than 1?
2. Furthermore, in those instances counted in question 1., how do i add up the numbers shown on column C? For example if only C4, C6, C7 show a number bigger than one, i.e 2, 5, 2 accordingly (and are on rows where column B is “yellow”), the sum of those value? I.e 9?
Hope this is clear. Thanks 🙂
Prolific Oaktree says:
You can do things like that by using the AND function and putting these logical tests inside of it.
Jonas Wind says:
Hey there! Thanks for the article.
I am trying to build an automated sheet for my online store to keep track of my stock & notify my manufacturer automatically when the runway for a specific item falls below a certain number of days.
For this, I would pull data from my shopify store with Zapier.
Using historical data of the last 7 (or 30) days, I would estimate
a) the average # of “item1” per order (also item2, item3, …)
b) average # of orders per day
Coupled with the absolute quantity for each item in my warehouse, I will know
–> how many days until I am sold out of a given item
and
–> send automated emails to my manufacturer once the runway of an item falls below a defined threshold (in my case the lead time to get new product, e.g. 30 days), also factoring in +/- accuracy error margins
–> result would look like “item1 will be sold out in 38-45 days”
For this, I need to count the number of times a specific word appears in the item’s title (as pulled directly from shopify)
Challenge: I sell items in bundles, which do not contain every item’s name.
Example:
I sell single origin cacao, the product titles all contain the country of origin, e.g. “mexico”.
My best selling product though is the “explorer’s box” which contains 3 of our bestselling strains (“mexico”, “venezuela”, “madagascar”).
So what I need to do in order to make the formula =COUNTIF(A2, “*mexico*”) work is to also look for the word “*explorer*” in the item’s title.
If any one of those is true, the output for my column “#of mexico sold” should be 1 (because in every “explorer’s box”, there is one “mexico”).
How can I include this OR function? I have the feeling that the operator “OR” in spreadsheets is not what I am looking for…
Thanks for your help!
Rebekah says:
Is there a way to count how many times a word is used in a sheet? I used =countif($B$2:$B$83,”*who*”) to find how many cells contained the word “who” within it. However, I want to count how many times “who” was used, because it can be used within a cell multiple times.
Thank you!
Andrew says:
How do I sort and count a column that is filled with 10 digit numbers. I want to count it by the end of every numbers.
i.e.
6001000011
6002000012
6001000111
6011000021
6003000013
I want to count them by the last 2 digits.
Please I need your help
Prolific Oaktree says:
I would consider using the RIGHT() function, grabbing the last two numbers, and then working with those.
Meg says:
how would i count the # of cells that contained one of several text strings but only count them once? for example, i’d like to count the number of cells with “blue” or “green” but only wanted a cell labeled “blue green” to count once
Prolific Oaktree says:
I made a video on this question and posted it to the Prolific Oaktree YouTube channel. Here’s the link to it.
https://youtu.be/ghr1tA_q2-Q
Gourav Kumar says:
i need help with same situation … if i have 2 or more yellow text in one cell then what function i will use pls suggest.. https://www.prolificoaktree.com/google-sheets-count-cells-containing-specific-text/
Shai says:
How would I count text within two different columns? For example, I’m trying to count how many customers said yes or no (column A) from Angi Business Center (column B). Not sure if I’m explaining this correctly, but any help would be appreciated.
Prolific Oaktree says:
Try the COUNTIFS formula. It allows for multiple conditions.
Conner Duberville says:
What if a cell contains a phrase twice for example what if some cells say “CH CH YM” and you want it to count both “CH” in the same cell?
Theis Svendsen says:
Thanks for this!
I’m trying to take it a step further, but can’t seem to get it to work.
Case is:
Column B contain country names. Column C contain either “xxxxx W” or “xxxxx L”.
I’ve made a table where I’d like the output of W/L counts of the specific country (into seperate cells of course).
How would I go about this?
Patti Hodges says:
How would this work to count more than one item in the column, say, “yellow” and “red” in your example.
Thanks
Prolific Oaktree says:
Try joining two conditions with the AND function.
ThomasU says:
Hey PO!
Can you help?
If I had a sheet which had two columns, that have words containing FLAG and FLAGGED, and I wanted to and up the total number of times these words both appear in total in a sheet, how can I do that please?
Thank you 🙂
Prolific Oaktree says:
The COUNTIFS function should be able to handle this for you. https://sheetshelp.com/countifs/