In this lesson, I’ll demonstrate 9 effective approaches on how to count rows and also cells with text in Excel. Moreover, these techniques will enable you to solve the problem that led you here. However, you will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.

## 📁 Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.

## Learn to Count Rows with Text in Excel with These 9 Methods

Here in this article, we will learn how to count rows or cells with text in Excel using different approaches. To be exact, I’ve provided a total of 9 methods down below.

**Method 1**

### 1. Using COUNTIF Function

Here we will be using the **COUNTIF** **function** to count rows with text.

⬇️⬇️ **STEPS **⬇️⬇️

- Firstly, copy down the following formula to the formula bar.

**=COUNTIF(C6:C14,”*”)**

- Press
**Enter**for results.

**Method 2**

### 2. Utilizing SUMPRODUCT Function

In this section, we will use the **SUMPRODUCT function**.

⬇️⬇️ **STEPS **⬇️⬇️

- Firstly, copy the following formula to the formula bar.

**=SUMPRODUCT(–ISTEXT(C5:C13))**

🔨** Formula Breakdown**

👉 **–ISTEXT(C5:C13)**

Boolean (TRUE / FALSE) values are transformed into integers using this. **TRUE **is changed to **-1** by the first – and to **1** by the second -. The **ISTEXT **works as If the supplied value is a text, returns **TRUE**; otherwise, it returns **FALSE **if the cell contains any other value or is empty in the range **C5:C13**.

👉 **SUMPRODUCT(–ISTEXT(C5:C13))**

Gives the total counts of rows with text.

👉 **Output**– 4.

- After that, press
**Enter**to get the results.

**Method 3**

### 3. Making Array Formula

Here we are making an array formula using **ISTEXT**, **SUM** as well as **IF** functions to determine the number of rows.

⬇️⬇️ **STEPS **⬇️⬇️

- Copy the following formula to the formula bar.

**=SUM(IF(ISTEXT(C5:C13),1))**

🔨** Formula Breakdown**

👉 **ISTEXT(C5:C13)**

The **ISTEXT **works as If the supplied value is a text that returns **TRUE**; otherwise, it returns **FALSE **if the cell contains any other value or is empty in the range **C5:C13**.

👉 **IF(ISTEXT(C5:C13),1)**

This part says that a text value in the range is found then return 1.

👉 **SUM(IF(ISTEXT(C5:C13),1))**

Provides us with the total number of rows that has text.

👉 **Output**– 4.

- After that, press
**Enter**to get the results.

**Method 4**

### 4. Using COUNTIF Defining Special Text

Here we will count rows if a specific text is found in the search array using the **COUNTIF function**.

⬇️⬇️ **STEPS **⬇️⬇️

- Firstly, copy the following formula to the formula bar.

**=COUNTIF(B5:B13, “*Ball*”)**

- After that, press
**Enter**to get the results.

**Method 5**

### 5. Counting Rows Using Various Criteria

Here we will define multiple criteria two to be exact and count the number of cells or rows satisfying them using **SUMPRODUCT**, **ISNUMBER** and **FIND** functions.

⬇️⬇️ **STEPS **⬇️⬇️

- Firstly, copy the following formula to the formula bar.

**=COUNTIF(B5:B13, “*Ball*”)**

- After that, press
**Enter**to get the results.

**Method 6**

### 6. Finding Non-Text Values

If you want to determine the number of rows which don’t have texts then follow this method. However, we are using the **COUNTIF function.**

⬇️⬇️ **STEPS **⬇️⬇️

- Firstly, copy the following formula to the formula bar.

**=COUNTIF(C5:C13,”<>*”)**

- After that, press
**Enter**to get the results.

**Method 7**

### 7. Counting Rows with Text Except Space Character

For instance, say we have a dataset of text and numbers we want to determine the number of rows that have text only. But there are cells that seem blank but have space. Excel considers space as a character or text. Using the **COUNTIFS function** we are telling Excel not to consider space as a character.

⬇️⬇️ **STEPS **⬇️⬇️

- Firstly, copy the following formula to the formula bar.

**=COUNTIFS(C5:C13,”*?*”,C5:C13,”<> “)**

- After that press
**Enter**to get the results.

Here as you can see we got 3 counts to cause the cross-signed cell **C8,** has a space. It can be intentional or maybe a mistake. Here we notified Excel not to take space as a character. If the cell was blank the function wouldn’t count it but if it had space the function would obviously count.

**Method 8**

### 8. Using SUBTOTAL and FILTER Functions

In this section, we are going to use the **Filter Tool **from the Data tab of the ribbon as well as two different functions which are **SUBTOTAL** & **COUNTIFS **functions.

⬇️⬇️ **STEPS **⬇️⬇️

- First, select the cells you want to count rows from.

- After that select the
**Filter**option from the**Data**tab of the ribbon.

- Now copy the following formula as
**function_num**is set to**103**to find all manually filtered out and concealed cells and the reference is**C5**.

**=SUBTOTAL(103,C5)**

- Now
**copy down the formula**along the column.

- After that copy the following formula in the Count column for counting rows with texts.

**=COUNTIFS(C5:C13, “*”, D5:D13, 1)**

- Now press
**Enter**for the result.

**Method 9**

### 9. Using VBA Code

In this section, we are going to use VBA code to determine number of rows or cells having text in them.

⬇️⬇️ **STEPS **⬇️⬇️

- Firstly you need to
**enable the Developer tab**. After that, choose the**Visual Basic**option from the**Developer**tab of the ribbon. You’ll see a window popping up.

- Select Module after selecting
**Insert**from the tab and write the following**VBA code**there and save it.

```
Sub TxtChekcer()
Dim counter As Integer
counter = 0
For i = 6 To 14
If Application.WorksheetFunction.IsText(Cells(i, 3)) = True Then
counter = counter + 1
End If
Next i
MsgBox "Number of rows with text is " & counter
End Sub
```

- Go to the
**Macros**option from the**Developer**tab of the ribbon and you’ll see this window below.

- The following results will show if you pressed
**Run**.

## 📄 Important Notes

`🖊️`

Be careful while using the code as there are a lot of terms and if you lose concentration the code may be incorrect.

`🖊️`

Use **Ctrl+Shift+Enter **for arrays if you are not using **Microsoft 365**.

`🖊️`

While writing formulas carefully see the suggestions and don’t forget the commas and parentheses.

`🖊️`

Be careful while defining criteria to be accurate as the dataset.

`🖊️`

Use** Alt+F11** for opening Visual Basic window as a shortcut.

## 📝 Takeaways from This Article

`📌`

Use of** COUNTIF**, **COUNTIFS**, **SUMPRODUCT**, **ISTEXT**, **ISNUMBER**, **SUBTOTAL** and** FIND **functions.

`📌`

To determine number of cells or rows having text in Excel using different functions.

`📌`

How to use multiple functions to create a formula.

`📌`

Counting rows without text.

**Conclusion**

Firstly, I hope you were able to use the techniques I demonstrated in this How to Count Cells or Rows with Text in Excel lesson. As you can see, there are a lot of options on how to do this. However, decide deliberately on the approach that best addresses your circumstance. Most importantly I advise repeating the steps if you become confused in any of the steps if you get stuck. Practice on your own after taking a look at the **Excel Count Rows with Text** Excel file in the practice workbook as I’ve provided above because practice makes a man perfect. Above all, I earnestly hope you can use it. Please share your experience with us in the comment box about how your experience was throughout the article and what we need to improve or add to make things better for you folks. However, if you have any problem regarding Excel spit it in the comment box. The **Excelden **crew is always available to answer your questions. In conclusion, keep educating yourself by reading. For more Excel-related articles please visit our website **Excelden.com**.