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