spreadsheetpoint.com
Open in
urlscan Pro
162.159.135.42
Public Scan
URL:
https://spreadsheetpoint.com/count-cells-with-specific-text-in-google-sheets/
Submission: On March 14 via manual from US — Scanned from DE
Submission: On March 14 via manual from US — Scanned from DE
Form analysis 3 forms found in the DOM
GET https://spreadsheetpoint.com
<form class="elementor-search-form" role="search" action="https://spreadsheetpoint.com" method="get">
<div class="elementor-search-form__toggle">
<div class="e-font-icon-svg-container"><svg aria-hidden="true" class="e-font-icon-svg e-fas-search" viewBox="0 0 512 512" xmlns="http://www.w3.org/2000/svg">
<path
d="M505 442.7L405.3 343c-4.5-4.5-10.6-7-17-7H372c27.6-35.3 44-79.7 44-128C416 93.1 322.9 0 208 0S0 93.1 0 208s93.1 208 208 208c48.3 0 92.7-16.4 128-44v16.3c0 6.4 2.5 12.5 7 17l99.7 99.7c9.4 9.4 24.6 9.4 33.9 0l28.3-28.3c9.4-9.4 9.4-24.6.1-34zM208 336c-70.7 0-128-57.2-128-128 0-70.7 57.2-128 128-128 70.7 0 128 57.2 128 128 0 70.7-57.2 128-128 128z">
</path>
</svg></div> <span class="elementor-screen-only">Search</span>
</div>
<div class="elementor-search-form__container">
<input placeholder="Search..." class="elementor-search-form__input" type="search" name="s" title="Search" value="">
<div class="dialog-lightbox-close-button dialog-close-button">
<svg aria-hidden="true" class="e-font-icon-svg e-eicon-close" viewBox="0 0 1000 1000" xmlns="http://www.w3.org/2000/svg">
<path
d="M742 167L500 408 258 167C246 154 233 150 217 150 196 150 179 158 167 167 154 179 150 196 150 212 150 229 154 242 171 254L408 500 167 742C138 771 138 800 167 829 196 858 225 858 254 829L496 587 738 829C750 842 767 846 783 846 800 846 817 842 829 829 842 817 846 804 846 783 846 767 842 750 829 737L588 500 833 258C863 229 863 200 833 171 804 137 775 137 742 167Z">
</path>
</svg> <span class="elementor-screen-only">Close</span>
</div>
</div>
</form>POST https://spreadsheetpoint.com/wp-comments-post.php
<form action="https://spreadsheetpoint.com/wp-comments-post.php" method="post" id="commentform" class="comment-form" novalidate="">
<p class="comment-form-comment"><label for="comment" class="screen-reader-text">Comment</label><textarea id="comment" name="comment" cols="45" rows="8" required=""></textarea></p><label for="author" class="screen-reader-text">Name</label><input
placeholder="Name *" id="author" name="author" type="text" value="" size="30" required="">
<label for="email" class="screen-reader-text">Email</label><input placeholder="Email *" id="email" name="email" type="email" value="" size="30" required="">
<label for="url" class="screen-reader-text">Website</label><input placeholder="Website" id="url" name="url" type="url" value="" size="30">
<p class="comment-form-cookies-consent"><input id="wp-comment-cookies-consent" name="wp-comment-cookies-consent" type="checkbox" value="yes"> <label for="wp-comment-cookies-consent">Save my name, email, and website in this browser for the next time
I comment.</label></p>
<p class="form-submit"><input name="submit" type="submit" id="submit" class="submit" value="Post Comment"> <input type="hidden" name="comment_post_ID" value="1017" id="comment_post_ID">
<input type="hidden" name="comment_parent" id="comment_parent" value="0">
</p>
<p style="display: none;"><input type="hidden" id="akismet_comment_nonce" name="akismet_comment_nonce" value="1d7a48d7cb"></p>
<p style="display: none !important;"><label>Δ<textarea name="ak_hp_textarea" cols="45" rows="8" maxlength="100"></textarea></label><input type="hidden" id="ak_js_1" name="ak_js" value="215">
<script type="rocketlazyloadscript">document.getElementById( "ak_js_1" ).setAttribute( "value", ( new Date() ).getTime() );</script>
</p>
</form><form class="SignupPopupBlockWrapper__SignupBlockForm-sc-1353rsc-3 cQThby">
<div class="SignupPopupBlockWrapper__FieldsWrapper-sc-1353rsc-0 fHNvMh">
<div class="SignupPopupBlockWrapper__GenericFieldWrapper-sc-1353rsc-4 ivThAQ">
<div class="styles__FieldRow-fwg4m8-3 Dotcg wisepops-field wisepops-field-email"><input class="styles__BasicInput-fwg4m8-0-input BnbMy wisepops-field wisepops-field-email" name="email" required="" aria-label="Eg: emily@gmail.com" type="email"
pattern="^[a-zA-Z0-9.!#$%&’*+/=?^_`{|}~-]+@[a-zA-Z0-9-]+(?:\.[a-zA-Z0-9-]+)+$" placeholder="Eg: emily@gmail.com" title="john.doe@example.com" id="wisepops-field-2289853-0"></div>
</div><button class="SignupPopupSubmitButton__InnerSubmitButton-sc-1jovqh8-0 haMLYJ">Get The Templates</button>
</div>
</form>Text Content
Skip to content
* Google Sheets Tips
* Formulas
* Charts & Graphs
* Top Courses
* Templates
Menu
* Google Sheets Tips
* Formulas
* Charts & Graphs
* Top Courses
* Templates
Search
Close
Twitter Youtube Linkedin
Home » Count Cells With Text in Google Sheets [2 Easy Functions]
COUNT CELLS WITH TEXT IN GOOGLE SHEETS [2 EASY FUNCTIONS]
* Sumit
* Last updated November 4, 2022
When you’re working with large datasets in Google Sheets, you will sometimes
need to count cells with text in Google Sheets. This could be names, ids, or
even dates.
But can Google Sheets count cells with text?
Luckily like most other spreadsheets, you can count text in Google Sheets.
OUR LATEST VIDEOS
VLOOKUP in Google Sheets: The Easy Guide
More Videos
0 seconds of 4 minutes, 34 secondsVolume 0%
Press shift question mark to access a list of keyboard shortcuts
Keyboard ShortcutsEnabledDisabled
Play/PauseSPACE
Increase Volume↑
Decrease Volume↓
Seek Forward→
Seek Backward←
Captions On/Offc
Fullscreen/Exit Fullscreenf
Mute/Unmutem
Seek %0-9
Next Up
How to Insert Bullet Points in Google Sheets (Keyboard Shortcut + Formula))
06:29
Live
00:25
02:19
04:34
And thanks to awesome formulas in Google Sheets, this can be done in seconds.
The count functions in Google spreadsheet count cells with text.
In this tutorial, I will show you a couple of scenarios where you can count
cells that contain specific text.
Table of Contents
* COUNTIF Syntax
* Can Google Sheets Count Cells With Text With Specific Values?
* Count Cells That Do Not Contain a Specific Text
* Count Cells That Contain Text (Anywhere in the Cell/Partial Match)
* How to Count Cells with Text in Google Sheets Using the COUNTA Function?
* COUNTA vs COUNTIF vs LEN
* Conclusion
COUNTIF SYNTAX
Using the COUNTIF function for Google Sheets to count cells with text is a
simple process. The syntax for the COUNTIF function is written as follows:
=COUNTIF(range, criterion)
Using this function, Google Sheets will count if contains a value in the range
that meets the criterion.
* The range, in this case, specifies to Google Sheets to count the number of
cells with text
* The criterion specifies which texts you want to be counted.
The COUNTIF Google Sheets formula counts cells with specific text by inputting
the specified texts as the criterion.
Let’s look at an example to help you understand better.
CAN GOOGLE SHEETS COUNT CELLS WITH TEXT WITH SPECIFIC VALUES?
Suppose you have a dataset, as shown below, and you want to quickly count the
number of times the name ‘Mark’ occurs in column A.
Below is the formula you can use to do this:
COUNTIF(A2:A13,”Mark”)
Here is how this above COUNTIF formula works:
* The first argument of this formula is the range where you have the data, In
this example, it’s A2:A13 which has the names
* The second argument is the criteria. This is what is used to check the value
in the cell, and if this criterion is met, then the cell is counted. In this
example, I have manually entered the name Mark in double quotes. If you have
this criterion in a cell, you can use the reference here.
The above formula does a conditional count by going through all the cells in the
range and counting those cells where the criterion text is present.
Note that the criterion text used in this formula is not case-sensitive. So
whether you use MARK, Mark, or mark, the result would be the same.
In this example, I have used a name, but it could be any text – such as ids or
numbers.
COUNT CELLS THAT DO NOT CONTAIN A SPECIFIC TEXT
You can modify the COUNTIF function in Google spreadsheet count number of cells
with text without using a specific text.
For example, if I have the dataset as shown below and I want to count all the
cells where the name is not Mark, I can easily do this with the COUNTIF
function.
Below is the formula that will give the count of all the cells where the name is
not Mark:
=COUNTIF(A2:A13,”<>Mark”)
The above formula uses the range of the cells as the first argument and the
criteria used is “<>Mark”. Here the not-equal-to sign needs to be within the
double quotes as well.
COUNT CELLS THAT CONTAIN TEXT (ANYWHERE IN THE CELL/PARTIAL MATCH)
In the examples above, we let Google Sheets count the number of cells with text
for the entire cell content .
So if we wanted to Google Sheets count if cell contains text specified, in this
case the name Mark, we used “Mark” as the criterion. This counted all the cells
where the entire cell content was ‘Mark’.
But let’s say, you have a dataset of full names (or some other text) along with
the name and you want to count all the cells that contain the name ‘Mark’, then
you can use the above formula.
In this case, you need to use the COUNTIF function with wild card characters.
Below is the formula that will give you the count of all the cells that have the
word ‘Mark’ in it.
=COUNTIF(A2:A13,”*mark*”)
In the above function, the criterion is flanked by an asterisk symbol (*) on
both sides.
As the asterisk sign (*) is a wildcard character that can represent any number
of characters in a formula. This means that where this formula checks for the
given condition, there could be any number of characters/words before and after
the criteria.
In simple terms, if the word Mark (or whatever your criterion is) is present in
the cell, this formula would count the cell.
Caution: One thing you need to remember when using the asterisk sign (*) to
count if cell contains text in Google Sheets is that it would consider the
condition met as soon as the criterion text is found. For example, in the above
example, in case the cell contains the text Market or Marketing, these would be
counted as these also contain the text ‘Mark’ – which is out criteria in the
function.
In case you want to use the function for counting text in Google Sheets by
checking for multiple criteria, you need to use the COUNTIFS function.
One of the questions I often get is whether you can use the COUNTIF function to
count the cells with colors. Unfortunately, you can not. However, here is a
tutorial where I show how you can easily filter and count cells based on color.
HOW TO COUNT CELLS WITH TEXT IN GOOGLE SHEETS USING THE COUNTA FUNCTION?
You can also use the COUNTA Google Sheets formula to count cells with text. The
COUNTA function counts the numeric values for the cells in the range that are
not empty.
This includes cells with error values and empty texts, but it does not count any
blank cells.
The syntax for the COUNTA function is:
COUNTA(value1, [value2], …)
Value1 (Required): Represents the values that you want to count.
Value2 (Optional): Represents additional values that you want to count, with a
limit of 255 arguments.
To make the COUNTA function count cells with texts only instead of all the
non-blank cells, you need to put in the arguments in the count function.
Here is a step-by-step guide on how to count cells with texts in Google Sheets
using the COUNTA Function:
1. Select the cell where you will put your formula.
2. Type in the formula =COUNTA(A1:A10)
3. This will return the number of cells with any text in them.
COUNTA VS COUNTIF VS LEN
The COUNTA function lets Google Sheets is like the “COUNTIF cell contains text”
function, but it can also be any other value. To let Google Sheets count cells
with specific text, you need to use COUNTIF functions instead. To count the
number of characters in a cell, you will need to use the LEN function instead.
CONCLUSION
Google Sheets counting cells with text is an easy topic to tackle once you know
what you’re doing. In this case, you can use count functions like the COUNTA
function if you want to count cells with any text and the COUNTIF Google Sheets
formula to count cells with specific text.
Hopefully, you were able to follow this guide on how to count cells with text in
Google Sheets and we’ve answered the question, “Can Google Sheets count cells
with text”. If you still need help, please let us know in the comments.
You may also like the following Google Sheets tutorials:
* * How to Get the Word Count in Google Sheets
* How To Remove Duplicates In Google Sheets
* Count Cells IF NOT Blank (Non-Empty cells) in Google Sheets
* Remove the First Character from a String in Google Sheets
* Count the Number of Characters in a Cell in Google Sheets
* IFS Function in Google Sheet
* Count Cells based on the Cell Color in Google Sheets
MOST POPULAR POSTS
5 SIMPLE WAYS TO HIGHLIGHT DUPLICATES IN GOOGLE SHEETS
IF CONTAINS GOOGLE SHEETS FORMULAS [2 CLEVER OPTIONS]
HOW TO MAKE MULTIPLE SELECTION IN DROP-DOWN LISTS IN GOOGLE SHEETS
HOW TO APPLY FORMULA TO ENTIRE COLUMN IN GOOGLE SHEETS
SUMIT
Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet
training to corporates and has been awarded the prestigious Excel MVP award by
Microsoft for his contributions in sharing his Excel knowledge and helping
people.
9 THOUGHTS ON “COUNT CELLS WITH TEXT IN GOOGLE SHEETS [2 EASY FUNCTIONS]”
1. John
March 4, 2021 at 7:57 am
Thank you
Reply
2. Carlos Alberto Teixeira
August 28, 2021 at 11:46 pm
Splendid and perfect instructions. Thanks a 1,000! And greetings from
Niterói, Rio de Janeiro, Brazil ❤️️ 👍 🖖
Reply
3. Rashmi
January 5, 2022 at 7:39 am
Great.. Thanks
Reply
* Matt C.
October 27, 2022 at 2:22 pm
What if I want to count the number of times Mark appeared in a cell? Say
example cell a1 contains mark / matt / alex / mark / jonathan / mark –
this would ideally say 3 counts of mark, but using your formula, it would
only count as 1 as they are in the same cell.
Reply
4. Gabriele
March 2, 2022 at 3:28 pm
Thank you, this is useful. What if instead of expliciting the text inside
the second argument I want to refer to a cell?
e.g. in C3 I have “Salad” and I want to count all cells in that range
containing *C3* that I know migh change.
It doesn’t work with the cell.
Reply
* Christopher Daniel
December 14, 2022 at 8:42 am
When typing the formula, click the cell C3 instead of typing it and don’t
use quote marks.
Reply
5. Ch. Umar Meraj
March 31, 2022 at 6:11 pm
Helpful, but I’m looking for some other scenario like if I have a list of
cities in the 1st column and the 2nd one contains multiple reasons.
Now I want to calculate all the data (from multiple sheets) on the main
sheet containing Cities in column A and Reasons in columns B, C, D, E & F.
Reply
6. Gabriele
July 15, 2022 at 3:12 pm
Hey Sumit, thanks for this post. What if instead of counting a simple text
value I want to count how many occurrences of a text that is contained in a
cell?
I tried =COUNTIF(A:A,”*A2*”) but it doesn’t work, Thank you
Reply
7. Arjo
October 29, 2022 at 5:40 pm
I am curious if it is possible to make the reference in the last part in the
formula for searching for a part of the cell value (usings the wild cards)
could be relative.
For example: i have a number of cells which I want the formula to check for:
B2: V C2: M D2: A E2: w
So I would the formula to check for the value in cell E2:
=COUNTIF(A2:A13,”*mark*”) would then become: =COUNTIF(A2:A13,E2)
But obviously, that doesn’t work as the wildcard/asterix is not present. So
I made this:
=COUNTIF(A2:A13,””*””&E2&””*””) but that also does not work.
I tried to use the textjoin formula. But that also doesn’t seem to work.
Is it possible to use a relative cell reference combined with the
asterix/wildcards to search for a specific string in a part of the
matrix/cells?
Reply
LEAVE A COMMENT CANCEL REPLY
Comment
Name Email Website
Save my name, email, and website in this browser for the next time I comment.
Δ
RELATED POSTS
HOW TO ADD PAGE NUMBERS IN GOOGLE DOCS [ULTIMATE GUIDE]
Emma Collins March 14, 2023
HOW TO CHECK WORD COUNT ON GOOGLE DOCS [EASY GUIDE]
Emma Collins March 14, 2023
5 BEST PPP LOAN FORGIVENESS SPREADSHEET TEMPLATES
Emma Collins March 14, 2023
HOW TO MAKE COLUMNS IN NOTION [COMPLETE GUIDE 2023]
Emma Collins March 13, 2023
Twitter Youtube Linkedin
* Write for us
* Contact
* About
* Privacy Policy
* Sitemap – Spreadsheet Point
* Write for us
* Contact
* About
* Privacy Policy
* Sitemap – Spreadsheet Point
SpreadsheetPoint is supported by its audience. When you purchase through links
on our site, we may earn an affiliate commission.
2023 © Spreadsheet Point
Access All Free Templates
Enjoy our custom-made Google Sheets templates for free via email access.
Get The Templates
No thanks
Update Privacy Preferences
An Elite CafeMedia Tech Publisher