Microsoft Excel provides a wide range of useful functions & methods to count cells with text or any other characters & strings. In this article, Iâ€™ll try to show & illustrate all the fruitful techniques that should make your regular work with Excel spreadsheets easier & save tons of time.
Download Practice Workbook
You can download our practice workbook here that weâ€™ve used to prepare this article.
Simple Ways to Count Cells with Text by Using Excel Formula
1.Â Â Using COUNTIF Function
To count cells under several criteria, the COUNTIF function does the best trick among all methods.
1.1 Using Asterisk(*) to Count Cells with Text
Here, in Column B, different sorts of words & values are present. You need to count the cells containing texts only.
Steps:
âž¤ In Cell D9, where weâ€™re going to find the result, type =COUNTIF(B4:B13,"*")
âž¤ Press Enter
So, the result will return as 4 as it excludes TRUE & FALSE. TRUE & FALSE are logical values in Excel this is why they wonâ€™t be counted as Texts. Weâ€™ll see later in the methods how we can bring back the texts TRUE & Â FALSE too.
Â Â Â Â Â Â Â Â Â Â
Â Â Â Â Â 1.2 Counting Text Cells with Partial Match
Â Â Â Â Â Â Â Â Â Â Â Now, we need to count cells that contain â€˜Apâ€™ among all texts.
Â Â Â Â Â Â Â Â Â Â Â Steps:
Â Â Â Â Â Â Â Â Â Â Â âž¤ In Cell D9, type =COUNTIF(B4:B13,"*Ap*")
Â Â Â Â Â Â Â Â Â Â Â âž¤ Press Enter.
Â Â Â Â Â Â Â Â Â Â Â So, among all cells from B4 to B13, 4 words are there that contain â€˜Apâ€™ inside the texts.
Â Â Â Â Â Â Â Â Â Â
Â Â Â Â Â Â Â 1.3 Counting Text Cells with Exact match
If we want to find the word â€˜Appleâ€™(Not Casesensitive) only from the cells, then type =COUNTIF(B4:B13,"Apple")
& press Enter.
So, 2 times this word has appeared exactly in the cells.
This formula excludes Apple Juice & will look for exact matches â€˜Appleâ€™ only.
But another fact is, this function is not casesensitive. Weâ€™ll have a solution for this issue in another method in this article.
Â Â Â Â Â Â Â Â Â Â
1.4 Counting Text Cells with Cell References
We can also select a cell that contains a word & then look for how many times this appears in the whole column or a range of cells. There are two cases here. Weâ€™ll search for a partial match first.
Steps:
âž¤ In Cell D7, type =COUNTIF(B4:B13,"*"&B5&"*")
âž¤ Press Enter.
The resultant value will be 3 as weâ€™ve searched for the texts (apple) lying in Cell B5 in all cells including itself.
Â Â Â Â Â Â Â Â Â Â
And if we want the exact match, then we have to type =COUNTIF(B4:B13,B5)
only excluding the quotation symbols & asterisks. So as here weâ€™re referring to the Cell B5 which contains the word â€˜appleâ€™. The result will return as 2 since exactly this word has appeared in Cells B5 & B6 only. Itâ€™ll exclude B4 which contains 2 words â€˜Apple Juiceâ€™.
1.5 Using a Wildcard to Count Cells with Fixed Position of the Characters in Texts
Now hereâ€™s an interesting part. Letâ€™s assume, we want to count the cells that contain a certain number of characters & the positions of characters will also be in consideration. So, as Iâ€™ve said, if we want to count the cells that contain exactly 5 characters & the 5th one will be â€˜eâ€™ then the formula will be
=COUNTIF(B4:B13,"????e")
Weâ€™ll get the count value as 2 as in our range of cells, only two words(apple & Apple) have exactly 5 characters among which the 5th one is â€˜eâ€™. And the word â€˜Apple Juiceâ€™ has not been counted here because this whole text contains 11 characters including a space.
Â Â Â Â Â Â Â Â Â
Â Â Â Â Â A Minor Limitation of Using COUNTIF Function & How to Solve It?
The COUNTIF function is really flexible to use but this function is unable to count more than 255 characters in a string. So to solve this issue, you have to concatenate two or more text strings by using Ampersand(&) between them. To know more about this, you can have a look at this recommendation from Microsoft.
2.Â Â Using COUNTIFS to Count Cells with Texts under Multiple Criteria
COUNTIFS is the subcategory of the COUNTIF function & this COUNTIFS will let you add multiple conditions to combine together.
Here, weâ€™ll count the cells under 2 criteria

 i) Cells with Texts &
 ii) Cells Excluding the Word Apes
To do this, we have to follow these simple steps
âž¤ In Cell D9, type =COUNTIFS(B4:B13,"*",B4:B13,"<>Apes")
âž¤ Press Enter.
So, now youâ€™re obtaining the number of cells as 3 under the mentioned criteria. And like before, as TRUE & FALSE are logical values in Excel they will be excluded from being considered as texts.
3.Â Â Applying SUMPRODUCT Functions to Count Cells with Texts
SUMPRODUCT is another useful function we can use to count cells with different types of requirements by integrating with other functions.
Â Â Â Â Â 3.1 Merging ISTEXT & SUMPRODUCT Functions to Count Cells with Texts
Weâ€™ll use the same dataset here to insert this function.
Steps:
âž¤ In Cell D9, type =SUMPRODUCT(<span style="fontsize: 10pt; fontfamily: tahoma, arial, helvetica, sansserif;"></span>ISTEXT(B4:B13))
âž¤ Press Enter & youâ€™ll see the result.
ðŸ”Ž Whatâ€™s happening here exactly?
SUMPRODUCT is generally used to sum all the numbered values in an array that can contain a huge range of cells along the columns & rows.
ISTEXT is a logical function here that finds out if the cells contain text strings (TRUE) or not (FALSE).
Before this ISTEXT function â€˜â€“â€™ (Double Hyphen, known as Double Unary too) has been used to convert the logical values gained from ISTEXT function into 1 (True) or 0 (False).
So, the SUMPRODUCT function lying outside will then sum up these all 1â€™s & 0â€™s and give you the result as Count values.
Â Â Â Â Â Â Â Â Â Â
3.2 Incorporating LEN & SUMPRODUCT Functions to Count Text & NonBlank Cells
Now, weâ€™ll incorporate SUMPRODUCT & LEN functions here. LEN function determines or counts the characters in a string that contains numbers of texts. So, if we get to know through this LEN function if a cell contains at least 1 character then weâ€™ll understand that cell contains text(s) or number(s) or any other value(s). And the SUMPRODUCT will be used here to count those cells like before.
Steps:
âž¤ In Cell D9, type =SUMPRODUCT((LEN(B4:B13)>0))
âž¤ Press Enter & youâ€™re done.
But here, youâ€™ll see the result returning as 9. Itâ€™s because the LEN function counts every single character including spaces too. It only excludes blank cells.
3.3 Combining SUMPRODUCT, FIND & ISNUMBER Functions to Count Cells with CaseSensitive Texts
If we want to count text cells along with casesensitive criteria then we have to merge SUMPRODUCT, FIND & ISNUMBER functions here. So, from our dataset, if we want to know how many times the word â€˜Appleâ€™(with Uppercase) has appeared in the cells then we have to follow these steps
âž¤ Type =SUMPRODUCT((ISNUMBER(FIND("Apple",B4:B13))))
in Cell D9
âž¤ Press Enter.
Youâ€™ll see the resultant value as 2 as this function has excluded the word â€˜appleâ€™(wth Lowercase) while executing the process.
Here, ISNUMBER is another logical function that determines if the string in a cell is a number(TRUE) or not(FALSE).
And FIND function looks for a specific text or value mentioned inside a fixed range of cells or array.
Read More: Count Cells that Contain Specific Text in Excel
Â Â Â Â Â Â Â 3.4 Counting Logical Texts by Using ISLOGICAL Function Inside
Now weâ€™ll count how many logical values are present in our dataset if we want to make them count as texts too.
Steps:
âž¤ In Cell D8, type =SUMPRODUCT((ISLOGICAL(B4:B13)))
âž¤ Press Enter.
So, youâ€™re seeing the resultant value as 2 as it counts only logical values from the range of cells.
And here ISLOGICAL function determines if the characters in a cell return as a logical value (TRUE) or not (FALSE).
Note: You might find DoubleHyphen or DoubleUnary as a Long Dash here as texts while reading this article but in the pictures, youâ€™ll find them with appropriate view.
4.Â Â Subtracting COUNT Function from COUNTA Function to Count Cells with Texts
And the last method weâ€™re to apply is SUBTRACTION between two functions. Here, the COUNTA function will count all the nonblank cells & the COUNT function will count all the cells with number strings. So, if we subtract the number of cells containing numerical values from the number of nonblank cells, weâ€™ll have our required result.
Steps:
âž¤ TypeÂ =COUNTA(B4:B13)COUNT(B4:B13)
âž¤ Press Enter & weâ€™re done.
This method includes all cells containing logical values or strings too & thatâ€™s why weâ€™ll get the result as 6.
Concluding Words
So these are all basic, easy & convenient techniques you can apply anytime in Excel sheets based on your required criteria. Hope, this article has helped you to learn all the methods to count cells with texts. If you have any questions or feedback then please comment here. You can also have a glance at our more interesting & informative articles on Excel functions & applications on this website.