Excel Formula to Count Cells with Text (All Criteria Included)

Microsoft Excel provides a wide range of useful functions and methods to count cells with text or any other characters and strings. In this article, you’ll get to learn all possible methods to count cells with text by using an Excel formula under different criteria.


Download Practice Workbook

You can download our practice workbook here that we’ve used to prepare this article.


4 Simple Ways to Count Cells with Text Using Excel Formula

In this section of the article, we will learn 4 simple methods to count cells with text using Excel formula.

Not to mention that we have used the Microsoft Excel 365 version for this article; you can use any other version according to your convenience.


1. Using COUNTIF Function

To count cells with text using Excel formula 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 and values are present. You need to count the cells containing text only.

Steps:

  •  Firstly, in cell D9, where we’re going to find the result, type the following formula.
=COUNTIF(B4:B13,"*")

Here, the range B4:B13 refers to the cells of the COUNTIF with Asterisk (*) column, and the COUNTIF function will count the cells within the range B4:B13 that meet our specified criteria.

  • Then, press ENTER.

excel formula to count cells with text

So, the result will be returned as 4 as it excludes TRUE and FALSE. TRUE and 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 and FALSE too.


1.2 Counting Text Cells with Partial Match

Now, we need to count cells that contain “Ap” among all texts. Let’s follow the instructions outlined below to do this.

Steps:

  • In cell D9, type the formula given below.
=COUNTIF(B4:B13,"*Ap*")

Here, the COUNTIF function will count the cells within the range B4:B13 that meet our specified criteria.

  • Following that, press ENTER.

Counting Text Cells with Partial Match by Excel formula

So, among all the 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 case-sensitive) only from the cells, we need to use the COUNTIF function of Excel. Let’s follow the steps mentioned below to do this.

Steps:

  • Firstly, use the following formula in cell D5.
=COUNTIF(B4:B13,"Apple") 

Now, the COUNTIF function will count the cells within the range B4:B13 that meet our specified criteria.

  • After that, press ENTER.

Counting Text Cells with Exact Match by Excel formula

So, this word has appeared exactly twice in the cells. This formula excludes “Apple Juice” and will look for exact matches for “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.


1.4 Counting Text Cells with Cell References

We can also select a cell that contains a word and then look at how many times this appears in the whole column or a range of cells. There are 2 cases here. We’ll search for a partial match first.

Steps:

  • Firstly, in cell D7, type the formula mentioned below.
=COUNTIF(B4:B13,"*"&B5&"*")

Here, cell B5 indicates the cell of criteria of the partial match and the COUNTIF function will count the cells within the range B4:B13 that meet the specified criteria in the above formula.

  • After that, press ENTER.

Counting Text Cells with Cell References by Excel formula

The resultant value will be 3 as we’ve searched for the text (apple) lying in cell B5 in all cells, including itself.

  • And if we want the exact match, then we have to type the following formula in cell D8.
=COUNTIF(B4:B13,B5) 
  • Then, press ENTER.

So here we’re referring to cell B5, which contains the word apple”. The result will return as 2 since exactly this word has appeared in cells B5 and 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 and the positions of characters will also be considered. So, as I’ve said, if we want to count the cells that contain exactly 5 characters and the 5th one will be ‘e’ then the formula will be-

=COUNTIF(B4:B13,"????e")

Using a Wildcard to Count Cells with Fixed Position of the Characters in Texts

We’ll get the count value as 2 as in our range of cells, only two words (apple and 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 and 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 learn more about this, you can have a look at this recommendation from Microsoft.


2. Using COUNTIFS to Count Cells with Text Considering Multiple Criteria

The COUNTIFS function is a subcategory of the COUNTIF function and this COUNTIFS will let you add multiple conditions to combine together.

Here, we’ll count the cells under 2 criteria:

  • Cells with Texts and
  • Cells Excluding the Word Apes

To do this, we have to follow these simple steps-

  • In Cell D9, type the formula mentioned below.
=COUNTIFS(B4:B13,"*",B4:B13,"<>Apes")

Here, the COUNTIF function will count the cells within the range B4:B13 that meet the specified criteria.

  • After that, press ENTER.

Using COUNTIFS to Count Cells with Text under Multiple Criteria by Excel formula

So, now you’re obtaining the number of cells as 3 under the mentioned criteria. And like before, as TRUE and FALSE are logical values in Excel, they will be excluded from being considered as texts.

Final output of method 2 to Count Cells with Text under Multiple Criteria using Excel formula

Read More: Count Cells that Contain Specific Text in Excel


Similar Readings


3. Applying SUMPRODUCT Formula to Count Cells with Text

The SUMPRODUCT function is another useful function we can use to count cells with different types of requirements by integrating it with other functions. The SUMPRODUCT function is generally used to sum all the numbered values in an array that can contain a huge range of cells along the columns and rows.


3.1 Merging ISTEXT and SUMPRODUCT Functions to Count Cells with Texts

Merging ISTEXT and SUMPRODUCT functions is a smart way to count cells with texts using Excel formula. We’ll use the same dataset here to insert this function. Now, let’s follow the procedure discussed in the following section of the article.

Steps:

  • In cell D9, type the following formula.
=SUMPRODUCT(--ISTEXT(B4:B13))
  • Now, press ENTER.

Formula Breakdown

  • The ISTEXT function 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 the ISTEXT function into 1 (TRUE) or 0 (FALSE).
  • So, the SUMPRODUCT function lying outside will then sum up all 1’s and 0’s and give you the result as count values.
  • Output 4.

Merging ISTEXT and SUMPRODUCT Functions to Count Cells with Texts in Excel

Consequently, you will see the following output on your worksheet, as demonstrated in the following image.


3.2 Incorporating LEN and SUMPRODUCT Functions to Count Text and Non-Blank Cells

Now, we’ll incorporate SUMPRODUCT and LEN functions here. The LEN function determines or counts the characters in a string that contains a number 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 function will be used here to count those cells like before.

Steps:

  • Firstly, in cell D9, type the formula given below.
=SUMPRODUCT(--(LEN(B4:B13)>0))
  • Subsequently, press ENTER.

Formula Breakdown

  • LEN(B4:B13)>0) → The LEN function counts every single character including spaces too. It only excludes blank cells.
  • Before this LEN function’ (Double Hyphen, known as Double Unary too) has been used to convert the logical values gained from the LEN function into 1 (TRUE) or 0 (FALSE).
  • Finally, the SUMPRODUCT function will then sum up all 1’s and 0’s and give you the result as count values.
  • Output 9.

Incorporating LEN and SUMPRODUCT Functions to Count Text and Non-Blank Cells in Excel

Here, you’ll see the result returning as 9 in cell D5.


3.3 Combining SUMPRODUCT, FIND and 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 and 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.

Steps:

  • Type the following formula in the output cell D9.
=SUMPRODUCT(--(ISNUMBER(FIND("Apple",B4:B13))))
  • Afterward, press ENTER.

Formula Breakdown

  • FIND(“Apple”,B4:B13) → The FIND function looks for a specific text or value mentioned inside a fixed range of cells or array.
  • ISNUMBER(FIND(“Apple”,B4:B13)) → The ISNUMBER function is another logical function that determines if the string in a cell is a number(TRUE) or not(FALSE).
  • Before this ISNUMBER function’ (Double Hyphen, known as Double Unary too) has been used to convert the logical values gained from the ISNUMBER function into 1 (TRUE) or 0 (FALSE).
  • Lastly, the SUMPRODUCT function will then sum up all 1’s and 0’s and give you the result as count values.
  • Output 2.

Combining SUMPRODUCT, FIND and ISNUMBER Functions to Count Cells with Case-Sensitive Texts in Excel

You’ll see the resultant value as 2 as this function has excluded the word “apple” (with lowercase) while executing the process.


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:

  • Firstly, in cell D8  type the formula given below.
=SUMPRODUCT(--(ISLOGICAL(B4:B13)))
  • After that, hit ENTER.

Formula Breakdown

  • ISLOGICAL(B4:B13) → The ISLOGICAL function determines if the characters in a cell return as a logical value (TRUE) or not (FALSE).
  • Before this ISLOGICAL function’ (Double Hyphen, known as Double Unary too) has been used to convert the logical values gained from the ISLOGICAL function into 1 (TRUE) or 0 (FALSE).
  • Lastly, the SUMPRODUCT function will then sum up all 1’s and 0’s and give you the result as count values.
  • Output 2.

Counting Logical Texts by Using ISLOGICAL Function to count cells with text by using an Excel formula

So, you’re seeing the resultant value as 2 as it counts only logical values from the range of cells.

Note: You might find Double-Hyphen or Double-Unary as a Long Dash here as text while reading this article, but in the pictures, you’ll find them with an appropriate view.

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


4. Inserting Subtraction Formula with COUNTA and COUNT Functions to Count Cells with Text

And in our last method, we’re going to apply a simple subtraction formula between 2 COUNTA functions. Here, the COUNTA function will count all the non-blank cells and 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:

  • Firstly, type the following formula in the output cell:
=COUNTA(B4:B13)-COUNT(B4:B13)
  • Then, press ENTER.

Inserting Subtraction Formula with COUNTA and COUNT Functions to Count Cells with Text in Excel

This method includes all cells containing logical values or strings too and that’s why we’ll get the result as 6.


How to Count Cells with Specific Text in Multiple Columns in Excel

In this section of the article, we will learn the steps to count cells with specific text in multiple columns. To do this we will simply use the COUNTIF function of Excel. Let’s use the steps outlined below to do this.

Steps:

  • Firstly, enter the following formula in cell B11.
=COUNTIF(B4:C8,"Apple")

Here, the range B4:C8 indicates the range where we will search and count for the text ‘Apple’.

  • Following that, hit ENTER.

How to Count Cells with Specific Text in Multiple Columns using Excel formula

Consequently, you will have the following output in cell B11. The output indicates that there are 2 cells in the array where there is an exact match for the text “Apple”.


How to Count Cells with Numbers in Excel

While working in Excel, we often need to count cells that contain numbers. We can do this simply by using the COUNT function of Excel. Let’s use the procedure discussed in the following section to do this.

Steps:

  • Firstly, use the following formula in cell D5.
=COUNT(B4:B13)
  • Then, press ENTER.

How to Count Cells with Numbers using Excel formula

As a result, you will get the count of the cells that contain numbers in cell D5 as marked in the following picture.

You can also use different ways to count cells with numbers in Excel.


Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.

practice section to count cells with text by using an Excel formula


Concluding Words

These are all basic, easy and convenient techniques you can apply anytime in spreadsheets to count cells with text by using an Excel formula under different conditions. I 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 and informative articles on Excel functions and applications on this website.


Further Readings

Nehad Ulfat

Nehad Ulfat

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

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo