How to Count Rows with Text in Excel (Easiest 8 Ways)

If you are looking for some of the easiest ways to count rows with text in Excel, then you will find this article worthy of it. Sometimes different texts and numbers and other types of variables get mixed up in a column. If you want to count rows based on texts, then let’s go through this article.

Download Workbook

8 Ways to Count Rows with Text in Excel

I have used the following table wherein the Size column I have both text and number variables. The number of rows will be counted here based on the texts in this column. Using the following example I will try to explain the methods.

data table

Method-1: Using COUNTIF to Count Rows with Any Text

To count the number of texts in the Size column you have to use the COUNTIF function here. Here I have added a column named Count for counting the number of texts.

using COUNTIF function

Step-01:
➤ Select the output cell in the Count column
➤Type the following function here

=COUNTIF(C5:C13,"*")

Here, C5:C13 is the range, * is the wildcard that matches any number of text characters.

COUNTIF function

Step-02:
➤Press ENTER
➤Now it will show up the value of texts in the Size column.

Excel count rows with text

Method-2: Counting Rows with Specific Text

Suppose I will now search in the Product column how many Shirts are there. To do this you have to use the COUNTIF function and enter the specific text name here.

count rows with specific text

Step-01:
➤ Select the output cell in the Count column
➤Type the following function here

=COUNTIF(B5:B13,"*Shirt*")

Here, B5:B13 is the range, and between the wildcards, Shirt is used for searching this text.

count specific texts

Step-02:
➤Press ENTER
➤Now it will show up the number of Shirts in the Product column.

count rows with specific texts

Method-3: Counting Rows Based on Multiple Criteria

Suppose you now want to find in the Product column how many Shirts and Shoes are there. To do this you have to use the SUMPRODUCT function here.

SUMPRODUCT function

Step-01:
➤ Select the output cell in the Count column
➤Type the following function here

=SUMPRODUCT(--(ISNUMBER(FIND("Shirt",B5:B13))+ISNUMBER(FIND("Shoe",B5:B13))>0))

Here, B5:B13 is the range

Using the FIND function you will find the desired texts Shirt and Shoe

ISNUMBER function will return an array containing 1 and 0. 1 will be for the case where criteria meet and 0 is for not meeting criteria

Finally, the SUMPRODUCT function will add up the values.

SUMPRODUCT function

Step-02:
➤Press ENTER
➤Now it will show up the number of Shirts and Shoes  in the Product column.

SUMPRODUCT function

Method-4: Counting Rows with Non-Text Values

If you want to find the non-text values like numbers in the Size column then you have to use the COUNTIF function here.

counting non-text values

Step-01:
➤ Select the output cell in the Count column
➤Type the following function here

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

Here, C5:C13 is the range, and before the wildcard, <> is used which means Not Equal to.

counting non-text values

Step-02:
➤Press ENTER
➤Now it will show up the number of numerical values in the Size column.

counting non-text values

Read More: How Excel Count Rows with Value (8 Ways)

Method-5: Counting Rows with Texts excluding Space Character

Suppose in the last row of the Size column there is a Space Character which is put here unintentionally. Using Method-1 in this case for counting texts will return the number of texts including this Space Character.

To count the number of texts excluding this Space Character you have to use the COUNTIFS function here.

count texts excluding space

Step-01:
➤ Select the output cell in the Count column
➤Type the following function here

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

Here, C5:C13 is the range, and *?* is the first criteria that will look for texts
In the second criteria <> is used which means Not Equal to Space

count texts excluding space

Step-02:
➤Press ENTER
➤Now it will show up the number of texts excluding Space Character in the Size column.

count texts excluding space

Method-6: Using SUMPRODUCT Function

You can use the SUMPRODUCT function for getting the number of texts in the Size column.

SUMPRODUCT function

Step-01:
➤ Select the output cell in the Count column
➤Type the following function here

=SUMPRODUCT(--ISTEXT(C5:C13))

Here, C5:C13 is the range,
The ISTEXT function will check out if there are texts and then will return TRUE and if there are no texts then it will return FALSE. Then will convert TRUE into 1 and FALSE into 0.
Then the SUMPRODUCT function will sum up the values.

SUMPRODUCT function

Step-02:
➤Press ENTER
➤Now it will show up the number of texts in the Size column.

Excel count rows with texts

Method-7: Using An Array Formula

You can count the number of texts by using the array formula as well.

array formula

Step-01:
➤ Select the output cell in the Count column
➤Type the following function here

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

Here, C5:C13 is the range,
The If function will return 1 if there is text and then the SUM function will sum up these values.

array formula

Step-02:
➤Press ENTER
➤Now it will show up the number of texts in the Size column.

array formula

Note
If you are using an Excel version other than Excel 365, then you may need to press CTRL+SHIFT+ENTER.  

Read More: How to Count Rows with Formula in Excel (5 Quick Methods)

Method-8: Counting Rows with Filtered Texts

Suppose you want to count texts in a filtered data table but in this case, you will get the total text values not only the shown values in the filtered column. But you can count the visible values only after filtering by following this method.

Before filtering data you just have to do some extra tasks like below.

counting filtered texts

Step-01:
➤Select Cell E5 of the Filtered Value column
➤Type the following formula

=SUBTOTAL(103,C5)

To identify all hidden cells, filtered out and hidden manually, function_num is used as 103
And C5 is the reference

counting filtered texts

Step-02:
➤Press ENTER
➤Drag down the Fill handle

counting filtered texts

Now you will get 1 for all of the rows.

counting filtered texts

Step-03:
➤Now you can filter the values according to your needs.
➤Select the output cell in the Count column.
➤Type the following formula

=COUNTIFS(C5:C13, "*", E5:E13, 1)

counting filtered texts

Step-04:
➤Press ENTER
➤It will return the number of texts visible in the Size column.

counting filtered texts

You can do the aforementioned task by typing only the following formula

=SUMPRODUCT(SUBTOTAL(103, INDIRECT("C"&ROW(C5:C13))), --(ISTEXT(C5:C13)))

counting filtered texts

Otherwise, use the following formula

=SUMPRODUCT(SUBTOTAL(103, OFFSET(C5:C13, ROW(C5:C13) - MIN(ROW(C5:C13)),,1)), -- (ISTEXT(C5:C13)))

counting filtered texts

Related Content: How to Count Rows with Data in Excel (4 Formulas)

Practice Section

For doing practice by yourself we have provided a Practice section like below for each method in each sheet on the right side. Please do it by yourself.

Practice

Conclusion

In this article, I tried to cover the easiest ways to count rows with text in Excel effectively. Hope you will find it useful. If you have any suggestions or questions feel free to share them with us.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo