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

Microsoft Excel provides a wide range of useful functions & methods to count cells with text or any other characters & 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.

## 4 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 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. #### 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 Text under Multiple Criteria

The COUNTIFS function 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. Cells with Texts &
2. 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 Formula to Count Cells with Text

The SUMPRODUCT is another useful function we can use to count cells with different types of requirements by integrating it 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(--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.

The 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 the ISTEXT function into 1 (True) or 0 (False).

So, the SUMPRODUCT function lying outside will then sum up all 1’s & 0’s and give you the result as Count values. #### 3.2 Incorporating LEN & SUMPRODUCT Functions to Count Text & Non-Blank Cells

Now, we’ll incorporate SUMPRODUCT & LEN functions here. The LEN function determines or counts the characters in a string that contains 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 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 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 the following formula in the output Cell D9:

`=SUMPRODUCT(--(ISNUMBER(FIND("Apple",B4:B13))))`

➤ 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, the ISNUMBER is another logical function that determines if the string in a cell is a number(TRUE) or not(FALSE).

And the FIND function looks for a specific text or value mentioned inside a fixed range of cells or array. #### 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 the ISLOGICAL function determines if the characters in a cell return as a logical value (TRUE) or not (FALSE). 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. Inserting Subtraction Formula with COUNTA and COUNT Functions to Count Cells with Text

And in our last method, we’re going to apply simple subtraction formula between two COUNTA 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 the following formula in the output cell:

`=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 spreadsheets to count cells with text by using an Excel formula under different conditions. 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.  