9 Ways to Count Rows with Text in Excel

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,”*”)

excel count rows with text COUNTIF function

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

excel count rows with text using SUMPRODUCT ISTEXT

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

excel count rows with text using array of SUM IF ISTEXT

  • 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*”)

excel count rows with text defining special text with COUNTIF function

  • 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*”)

excel count rows with text using SUMPRODUCT ISNUMBER FIND function

  • 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,”<>*”)

excel count rows with text using COUNTIF function

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

excel count rows with text without space 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.

Filter

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

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

Using filter counting rows


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.

VBA code window

  • 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

Vba code for excel count rows with text

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

excel count rows with text VBA


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

(Visited 28 times, 1 visits today)
Nashid

Nashid

Hello there. I am Nashid, an Engineer. I Love music, traveling, sports and gaming. I have recently grown interest in DATA SCIENCE and wish to build a career over it. Excel is one of the greatest modern digital creation of human. Life simply gets easier. My Articles are meant to lead you to that easier life with efficiency. If you need any kind of help regarding my articles even anything else try contacting me on social platforms of which I've provided. Keep learning. Wish me luck and Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo