Excel Formula to Count Cells with Text (Download Free Workbook)

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.

           Count cells with texts with COUNTIF & asterisk


         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.

           Count cells with text partial match


        1.3 Counting Text Cells with Exact match

If we want to find the word ‘Apple’(Not Case-sensitive) 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 case-sensitive. We’ll have a solution for this issue in another method in this article.

           Count cells with text by exact match


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.

           Count cells with texts under cell reference

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’.

Count cells with texts under cell reference


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.

          Count cells with text using wildcard

         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-

    1. i) Cells with Texts &
    2. 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.

Count cell with texts using countifs function


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="font-size: 10pt; font-family: tahoma, arial, helvetica, sans-serif;">--</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.

           Count cells with texts by SUMPRODUCT & ISTEXT functions


3.2 Incorporating LEN & SUMPRODUCT Functions to Count Text & Non-Blank 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.

Count cells with texts by SUMPRODUCT & LEN functions


3.3 Combining SUMPRODUCT, FIND & ISNUMBER Functions to Count Cells with Case-Sensitive Texts

If we want to count text cells along with case-sensitive 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.

Count cells with text by SUMPRODUCT, FIND and ISNUMBER funtions


        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).

Count cells with text by ISLOGICAL & SUMPRODUCT

Note: You might find Double-Hyphen or Double-Unary 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 non-blank 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 non-blank 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.

Count cells with text by subtraction between COUNTA & COUNT functions


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.


Related Articles

How to Count Cells with Specific Text in Excel (Both Case Sensitive and Insensitive)

How to Count Frequency of Text in Excel (Using VBA)!

Finding out the number of duplicate rows using COUNTIF formula

Count Cells that Contain Specific Text in Excel

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo