Google Sheets - Count Cells Containing Specific Text

There are a few ways in Google Sheets to count cells with certain text. You can count the cells that contain only certain text, or you can count cells that contain certain text inside of a long string of characters.
See the article with the linked sheet here: bit.ly/2KqLLT4
Counting cells WITHOUT certain text: sheetshelp.com...
Counting cells with specific capitalization, follow the second example in: sheetshelp.com...
Learn more from Prolific Oaktree:
🌳 Next Video - Share Your Spreadsheet With Others - • Google Sheets - Share ...
🌳 Related Playlist - Google Apps | Full Tutorials - bit.ly/google-...
#googlesheets #countif #spreadsheet

[@arianalai5546]

EZ! See? Why can't more tutorials be like this? Simple and straight to the point. Good job man, thank you!

[@ProlificOaktree]

Anytime! Glad you liked it.

[@kismetkaiser3160]

This saves me more time when it comes to tallying numbers of respondents. Thank you so much!

[@amandathompson5298]

Thank you for this! We are getting ready to start distance learning and I need a way to keep track of student participation in class. I needed this to be simple and user friendly since I barely use sheets. This was exactly what this teacher needed.

[@ProlificOaktree]

Best of luck!

[@joeydawn3869]

How do I do this but with more than one value in different columns?

[@HritwRaje]

COUNTIF (range, "*") for counting the amount of text in the selected range.

[@MUSIC-zc3tq]

Thank you very much man. I searched this solution at least 5+ hour, finally got this.

[@ProlificOaktree]

Glad it helped

[@JKXYGolden]

Thank you! So glad there are people like you who make helpful videos like this.

[@ProlificOaktree]

Hey, I know how to have a good time!

[@JessicaMalvido]

You saved my life. Thank you!

[@ProlificOaktree]

You're welcome. Glad it helped!

[@pamplemoussejuice8427]

THANK YOU THIS MADE MY LIFE WAY EASIER GOD BLESS

[@elisamartinez8180]

Thank you! Just what I was looking for. Thanks for explaining it so clearly/simply.

[@ProlificOaktree]

You're welcome! I'm glad you liked it.

[@climberdad]

Just used this for my climbing gyms problem tracker sheet. Amazing little trick! Thank you so much for sharing this!

[@ProlificOaktree]

Awesome, glad it helped out.

[@ayumiwaru]

Thanks and i really really appreciate how simple and short you keep this video!!

[@ProlificOaktree]

Glad it was helpful!

[@JohnBarsevich]

Awesome explanation. Clear concise. Just what I was looking for. Thanks!

[@shivamkapahi]

Hey, it was so simple to follow and was particularly what I was looking for. I wanted to count cells based on color coding, could not find a way to do in google sheets. But my different colored cells had different text, so this worked amazingly. Also, this saved me from running custom formula script which was always loading and was way too frustrating.

[@PirozokTV]

It helped me to grasp what refers to what. Thanks a lot !

[@KevinMDoyle]

Very helpful and easy to understand. What if one wishes to count the number of times a certain word shows up in one column combined with a certain word in another column? For instance, one column is gender and another column is Degree in Engineering, and you wish to count the number of females with a degree in engineering....

[@starswater]

Thank you! This was a huge help.

[@ProlificOaktree]

You're welcome!

[@askdhuwuw]

Wow, I did not know how much I needed this. Thank you!

[@ProlificOaktree]

You're so welcome!

[@rd14italia]

Instead of typing in what I want to look for, could I tell it to look for an exact match to text that is in a specific cell? For instance, if the word yellow was in cell A2 could I just use in the formula A2 as my criteria?

[@shannathompson4824]

did you ever find out? thanks!

[@traior246]

If the Sheet's own explanation can be half as clear as yours Thanks

[@CraftyLad]

thank you so much!

[@radiantrosecure3721]

This makes things so much easier! Thank you :)

[@ProlificOaktree]

Happy to help!

[@gailmontes9922]

Thank you very much for the help, Sir!

[@ProlificOaktree]

Glad it helped!

[@gouravk8007]

this video is so helpful thank you so much.... 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

[@profitloverswithmelaniemil8369]

Thank you!!! I knew there would be a simple solution but could not figure it out. Great video!

[@ProlificOaktree]

Glad it helped!

[@sohailsayyed9719]

Thank you for the solution

[@rachelzirbes6524]

Thank you so much for this video! It was very helpful pulling data for an RFP I need to put together last minute.

[@ProlificOaktree]

Awesome, glad it helped.

[@saintgtx]

Extremely helpful & straight to the point! Liked & subbed 🙏

[@fxcg]

should've known that! Thank you anyway for the video, you helped me a lot!

[@ProlificOaktree]

Glad I could help!

[@esgallamarketing]

Si te sale un error en la versión en español de esta fórmula, es porque tienes que usar el punto y coma en lugar de la coma. If you get an error in the spanish versión, it's because we have to use the semicolon instead of comma.

[@davidac4824]

gracias!

[@josuamariano3439]

Thank you for this!

[@ProlificOaktree]

Glad it was helpful!

[@captoshuragnarok7444]

Thank you so much!

[@cowgoesgrrr]

how would i go about counting how many times the letter "c" shows up in that range if sometimes there is two "c" per column? its for my work log sheet and sometimes i do two checks a day (represented as "cc"). so far it only counts how many columns have "c" as apposed to how many times "c" is there.

[@MhachautauquaOrg]

A little late for you, but I found it: SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range,"c",""))) My sheet has people putting in times for each date (10am, 2pm), so I'm counting "m"

[@mydigitalmarketingjourney1087]

That was fantastic and simple. Thank you so much :)

[@truwebs]

Nice example. But is this possible?? I would like to count the number of * in a range of cells. For example I have range C2:N28. The number of * in these cells could be 0-3 at the current time. Is it possible to count the * character in this way?

[@PaigeNextDoor]

Super helpful, thank you! What if I want to count "blue" and "yellow" ?

[@jonathanmarkskydd1491]

Thank you. And also cudos on having the office outdoors in a lake house in Banff.... on the bucketlist.

[@ProlificOaktree]

I've actually been to Banff, it's incredible.

[@arlemchowdhury6372]

Thank you! So helpful 😊

[@derekjbarbee]

Thank you, I was looking for something like this, and could not find an option.

[@ProlificOaktree]

Glad I could help

[@mschwambach]

This was incredibly helpful. Thank you so much.

[@ProlificOaktree]

Glad it was helpful!

[@mmosier52]

I keep getting Zero. The cell I am looking to count is set up as drop down boxes. Does this change anything?

[@ProlificOaktree]

No, it shouldn't. Drop downs are still just values.

[@gempossible6578]

Did you ever figure out why your formula returned a zero? I’m having this exact issue. Thank you!

[@pattybruno01]

Thank you. This was just what I was looking for!

[@ProlificOaktree]

Glad to hear it!

[@WhaToCook]

Very helpful thanks!

[@ProlificOaktree]

You're welcome!

[@en89hl]

Amazing!

[@SaMzAii]

Thank you this is a great video and simple

[@ProlificOaktree]

You're welcome!

[@MiguelReyesDeveloper]

Thank you so much.

[@brandiwyatt2027]

Thanks!!

[@florendogalang4250]

Thank you

[@user-gw5wh3xu7b]

How would I go about searching for part of the word if the words determined by another cell, e.g =COUNTIF(B2:B9,A1) where A1 is yellow, but I'd like it to search like "*yellow*"

[@tatianagomezramirez4298]

I'm also trying to find this!

[@tatianagomezramirez4298]

Just found it! =COUNTIF(C2:C9, "*"&A1&"*")

[@andrewloucks3163]

@@tatianagomezramirez4298 THANK YOU!!!! I've been looking for this everywhere.

[@tatianagomezramirez4298]

@@andrewloucks3163 tell me about it! I almost didn't find it... tried it and it worked perfectly.

[@Yutani_Crayven]

@@tatianagomezramirez4298 Thank you. You are a GOD.

[@jeffkurtz5611]

Beautiful. Thank you

[@ProlificOaktree]

You're welcome!

[@LeoCRoderick]

thank you!!!!

[@UtPal_AXOM]

HOW TO CHECK THIS FOR TOTAL NUMBER OF NUMBERS/NUMERICALS

[@matthewadcock6876]

Thank you, exactly what I was looking for. Now I just need to work out how to check for if it contains 2 specfic strings in the cell.

[@ProlificOaktree]

Maybe something with the AND function before it.

[@emmagroen]

Thanks for the video! I have a question about the second column. Would it be possible to count like that while using a cell mention? So instead of "*yellow*" you would want to select (lets say) cell A1 that has the text 'Yellow' in it. And it would still count ever cell yellow is mentioned. (Maybe a formula l ike =countif(C2:C9, A1"**")??) I need to do this for a lot of different names so I want to make it as easy as possible so I dont have to manually type every word.

[@ProlificOaktree]

Try something like "*"&A1&"*"

[@aftermathgg4344]

Thanks man. really helped

[@ProlificOaktree]

Cool, glad it was what you were looking for.

[@siddique_e_akbar_media]

Thanks

[@defonotmario]

Is there a way to use COUNTIF an ignore blank cells? I am using this formula in every cell of column E: =COUNTIF(A:A,C2) in cell E2, =COUNTIF(A:A,C3) in cell E3, =COUNTIF(A:A,C4) in cell E4, so on and so forth. I'm using this formula to find the name in C2 and to find how many names in column A match that name. I have this formula in cells that reference a blank C cell. This returns a 0, which I do not want. Can I have my COUNTIF formula ignore the black cell and not return any data?

[@ProlificOaktree]

You could use an if statement with the ISBLANK function. Look here for more support.google.com/docs/answer/3093290?hl=en

[@defonotmario]

awesome, thank you so much!

[@maryamsofea27]

Thank you for helping me!

[@ProlificOaktree]

You're welcome!

[@plentyofpaper]

Very helpful. What I noticed on trying it though is that if one cell contains "Yellow Yellow" the cell will be counted once. I have an application where I would like it to be counted twice. Do you know of a good way to do this? Edit: For my purposes, I was able to come up with the workaround: countif(RANGE, "*Yellow*")+countif(RANGE, *Yellow, Yellow*) Although I'm never looking for more than 2 entries, and they will always appear consecutively, which is limited. So I'm set, but if there's a better way, it could still prove helpful.

[@timmiller5217]

This would work better for your purposes: =SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range,"Yellow","")))

[@plentyofpaper]

@@timmiller5217 Thank you! I'm past the point where I need this, but with some luck, somebody may find this helpful down the road!

[@gyanendrakumarmusician]

Thank u veri veri Mach sir

[@ProlificOaktree]

You're welcome!

[@nlcwilson2017]

Thank you boss!

[@ProlificOaktree]

Anytime dude.

[@harryblock_3826]

Is there a way that i can count a category, for example, how many times the word "yes" appears in Column A, then from those rows, it also contains a "yes" in another column". For example: A B Yes No Yes Yes Yes Yes Yes' in A = 3 Yes' In B = 2 Yes' In A and B = 2 Yes' in A, but not B = 1 How would I do those bottom two?

[@ProlificOaktree]

Yes, see here. sheetshelp.com/count-cells-using-an-or-condition/

[@mrbeanskids2123]

hello, so i changed the letter/number part to a cell so whatever is in that cell it comes up, i have multiple usernames in that cell so how would i do a * because it won’t let me do that, thanks!

[@diegooli9253]

If I have 2 yellow words in C2 for example, how can I do for count both?

[@timmiller5217]

Try this instead: =SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range,"Yellow","")))

[@worldtraveler419]

Great videos, very helpful. Have a question with Google Sheets, hoping you can help me. I'm setting up a budget. Each transaction has a separate cell from a drop down to categorize it (household, entertainment, etc) and then I have a column next to that for the $ amount associated with the transaction. How can I auto sum for each category and have a running total on the side for each? Any help would be appreciated. Thanks.

[@ProlificOaktree]

use SUMIF

[@ddzim]

not sure if it's new or why this would be the case for macs, but for me it needed a semi colon after the range for it to work - commas always give me a parsing error

[@ProlificOaktree]

You use semi-colons instead of commas if your locality is set to somewhere that uses commas as decimal separators.

[@ddzim]

@@ProlificOaktree oh, right, good to know, thanks!

[@alexandermotal5979]

Perfect video

[@ProlificOaktree]

Glad you enjoyed it

[@Longliveicecream]

Thank you 🙏

[@ProlificOaktree]

You're welcome!

[@sitict1836]

just wanna ask. for example in an election, what is the formula for showing the highest answered specific text

[@rosspeterson2658]

How do you have it where it’s two factors? (For example the words “Yellow” and “car”)

[@ProlificOaktree]

Use COUNTIFS

[@mercedescavani]

thank you!!

[@ProlificOaktree]

You're welcome!

[@viktorbill8125]

Thanks man.

[@ProlificOaktree]

No problem, glad you like it.

[@tetuko1731]

Thanks You Very Much

[@ProlificOaktree]

You're welcome.

[@DavidHar]

This doesn't work outside of the US, does anyone have the non-US version of this?

[@ProlificOaktree]

It works in most countries unless you use a comma as a decimal separator in which case you need to switch the commas out for semicolons.

[@memena]

How do we do it using apps script?

[@victoriavonheals2384]

Hi, mine keeps coming out as a percentage instead of a number. Is it a settings issue? I use my tablet, not the pc. Should I try on pc?

[@ProlificOaktree]

Just a formatting issue. You should be able to control that on any device and it should be device independent.

[@louiearbelastimoso4665]

Thanks bro

[@ProlificOaktree]

Welcome

[@OfficialMateiParvu]

I am trying to do this with a date selection. For example i have a customer list with the date that they opted in, and i want to count how many opted in in certain months. I am trying this but for some reason it is not working with the date format

[@ProlificOaktree]

A month value is not text in a spreadsheet. You need to do something like =COUNTIF(MONTH(A1)=10). MONTH returns the value of the month from a date. That formula would be for October.

[@Jaluvshuskies]

@@ProlificOaktree I tried this as well, but resulted in "N/A" error & said "wrong number of arguments to COUNTIF. expected 2 arguments, but got 1 arguments" My formula is: =COUNTIF(MONTH(F8:F525)=10) For my specific situation, I have a spreadsheet that documents user tickets - and we have a date column that's in format dd/mm/yy. I'm wanting to track how many tickets we get per month, so counting # of cells under x month (and if possible, under x year) So I've been trying to specify "start with 9" for month, and "end with 19" or "end with 20" for year ---> and then count the # of cells that meet this criteria Do you know how I can do this? Is it possible with the countif for month value? Before you mentioned that I can't use a month value, this is what I tried: I tried =COUNTIF(F8:F525, "9*") to count # of cells in september, but result was 0 (which isn't true) I tried =COUNTIFS(F8:F525, "9*", "*19") to count # of cells in september 2019, but got error N/A (expects all arguments after pos 2 to be in pairs) Sorry for the ramble, I figured it could help to show you what I've done so far

[@filthycasual6619]

THANK YOU

[@ProlificOaktree]

You're welcome, I'm glad you liked it.

[@filthycasual6619]

@@ProlificOaktree sat down to learn Google Sheets yesterday was stuck on something for 20 minutes that this function to help me do perfectly

[@paulhartwell5591]

is it possible to have multiple "*" or keywords counted as one For example: COUNTIF (range, "*", "*") and so on

[@ProlificOaktree]

YTry COUNTIFS, it should do what you are looking for.

[@meganwallace1642]

Can you do it over multiple google sheets?

[@ProlificOaktree]

Multiple Sheets inside of one file but not multiple files.

[@edtexier]

So how do i count the number of Yellow entries in a specific number of row cells e.g. D3, L3 & N3

[@ProlificOaktree]

Try this kzbin.info/www/bejne/f6awhKyqeZ6naZo

[@shortskirtshortfuse3655]

Oh my gosh, thank you.

[@ProlificOaktree]

Oh yeah!

[@genr89]

Need help and I hope someone will notice.. In the video: the result was the total YELLOWs in COLUMN B, row 10 but what I wanna achieve is the total of the color red. Is it possible to automatically change the criteria even without editing the text?

[@Moore729]

No, you have to edit the text within the formula for the correct data to be counted. Instead of typing “yellow” you would type “red” leaving everything else the same within the formula.

[@GuardianHooks85]

Is it possible to do this over multiple sub sheets in a single sheet?

[@ProlificOaktree]

Yeah, formulas can always reference other sheets in the same file. Type the formula until you get to the reference, then go to the other sheet and click on the cell.

[@jasonbaker8468]

Great tutorial! I have been using a formula containing a google mapping API that I can only call 1000 times per day on my account to record mileages from Point A to Point B. My spreadsheet contains thousands of these cells. My workaround has been to have my team paste the formula, wait for it to execute, then copy and paste back the resultant data as "values only". The problem is, sometimes we miss doing the copy/paste and so then each time that spreadsheet is opened, the formula gets called over and over again, thus wasting calls and using up my 1000 prematurely, from one day to the next day. So my question is: Can I utilize a COUNTIF or similar function which can tell me if there are formulas on a sheet. At this point, Google mapping is the only formula on the spreadsheet, but if the counting had specificity for parts of my formula, that would be cool.

[@ProlificOaktree]

You could try the ISFORMULA function.

[@CheapExplorer]

what if i need to count several words? example, total # of yellow, green and red?

[@ProlificOaktree]

You could copy and paste the text of the formula and switch out the words after you paste it into another cell.

[@CheapExplorer]

@@ProlificOaktree let me try it out, thank you so much!

[@SumeetAgarwal]

What if the range i want to refer is in a sheet that is in another drive?

[@ProlificOaktree]

Look at this video kzbin.info/www/bejne/rHOoi5yJdplsg68

[@marleyswindells8680]

rather than have "yellow" how would you go about using a cell address for the key word? thanks

[@ProlificOaktree]

Sure. Type the value yellow with no quotes in cell A1. Use the formula =COUNTIF(B2:B9,A1).

[@JmiahCovers]

@@ProlificOaktree But this only works if the cell matches exactly. What about if you want to find the count of occurences of a word anywhere within the range, such as how you showed in the second column?

[@ProlificOaktree]

@@JmiahCovers I fixed up the sheet with some new formulas that new help. It's linked to the blog post here www.prolificoaktree.com/google-sheets-count-cells-containing-specific-text/

[@joyceperreira]

How do you count how many cells have the same text as another cell without typing what the contents of the cell each time??? Thanks

[@ProlificOaktree]

=COUNTIF(A1=$B$1) to check if A1 is the same as B1 and you can copy the formula all the way down column A and it will only look at B1 because the dollar signs prevent B1 from changing.

[@tsrmksw]

COUNTIF(range; criterion) EXAMPLE COUNTIF(A1:A10; ">20") now its semicolon! ‼

[@ProlificOaktree]

The commas are semi-colons in certain countries. I used to have that disclaimer in videos and then I forgot about it. I should probably add it again!

[@gothamfury]

Is there any way to count if one column has the word "Truck" AND if the column next to it has the word "Red" ?

[@ProlificOaktree]

Yes, the COUNTIFS function accepts multiple criteria.

[@gothamfury]

Prolific Oaktree Thanks!

[@ProlificOaktree]

You’re welcome!

[@archgyanrevit]

How do you calculate only numbers in cells containing both text & numbers?

[@ProlificOaktree]

The regular COUNT or SUM, depending on what you mean by calculate.

[@smallbusinesshelper1]

In Google Sheets, I'm trying the following, but it's not working... =COUNTIFS(EventAttendees!$A$2:$A,{"*fareham*","*henry*","*college*",},EventAttendees!$I$2:$I,{"liam","test",},EventAttendees!$G$2:$G,$C4) WHAT I'M TRYING TO ACHIEVE. I need to know: How many bookings are made for 'each location', for each 'hour of the day'; but 'exclude any test entries'. a) count (in the logs tab 'EventAttendees'): All the bookings matching certain locations (column: a) containing: fareham OR henry OR college, b) but must NOT include 'test entries' made by my customer ... identified by excluding the records (also in the logs tab 'EventAttendees' but different column; column 'i') where the 'name field' (column: i) indicates it's a test entry made by my customer (eg.liam OR test). c) then, count all the above matches that also equal 'hour of day', seen in my immediately (same worksheet tab) the adjacent column eg.c4 says "0" (for 12am), c5 says 1 (for 1am) and so on. So I hope to get 'booking totals' in my column D (Fareham Signups) alongside my predefined hours of day column C. I hope that makes sense? I suspect that I am trying to parse too many arguments/criteria - perhaps likely using the wrong kind of formula/function. Sorry, I'm not so great with advanced Google Spreadsheets.

[@mazt3rst3w67]

What do I do to detect an entire column?

[@ProlificOaktree]

For column A, it would be (A:A).

[@ucctemp5617]

How would I do this if the data was on another sheet?

[@ProlificOaktree]

This covers how to use different types of cell references including to another sheet.

[@propfirm_ea]

doesnt work

[@henribano5119]

Big UP

[@brytonmassie]

What if i want to count multiple ranges? I cant do =COUNTIF('Sheet 1'!D:D,'Sheet 2'!D:D, "Yellow")

[@ProlificOaktree]

Try =COUNTIF({'Sheet 1'!D:D,'Sheet 2'!D:D}, "Yellow") or =COUNTIF('Sheet 1'!D:D, "Yellow")+=COUNTIF('Sheet 2'!D:D, "Yellow")+

[@ConsulthinkProgrammer]

Nice.. I Improve formula to count frequency :) kzbin.info/www/bejne/aKqahJd4lr2Zb68

[@rorytait]

Is there a function for picking out certain parts of an email address? For instance with email accounts could I sort by @gmail.com? Thanks so much!

[@ProlificOaktree]

Try going to the Data menu then Split text to columns...Choose the @ as a custom separator. Then you could sort the resultant table by the text after the @.