How Do I Count Cells with Text in Excel

You need to count how many cells contain text within your data set for many different operations. Today we will show you how to count cells with text in Excel. For conducting the session, we are using Excel 2019. Feel free to use your preferred version.

First things first, let’s get to know about the example workbook of today’s session

Excel sheet - How Do I Count Cells with Text in Excel

We have a table of two columns, Products and Quantity. Our Products column has text values where the Quantity column is to store number values.

But to add complexity, we have mistyped a couple of values. This is a basic data set. In the real scenario you may encounter large and complex data sets.

Practice Workbook

You are welcome to download the workbook from the link below.

1. Count Text Cells in Excel

1.1. Count using COUNT functions

Within the family of COUNT functions, you will see several functions like, COUNT, COUNTA, COUNTIF. In this section we will make use of these functions to compute text cells in excel.

Let’s see our 1st formula. It will be using COUNTA and COUNT

COUNTA(range)-COUNT(range)

COUNTA/COUNT - How Do I Count Cells with Text in Excel

COUNTA function will count every cell within the range apart from empty cells.

COUNT function will count every cell that has a number format value.

Write the formula in the Excel.

Counta/count result - How Do I Count Cells with Text in Excel

Here we have counted for the Products column. There are 10 rows in this column, where 1 row is empty.

COUNTA function will return 9 here since it will count anything but empty cells.

COUNT function will return 2, as it only counts number values

Subtracting these two we got 7. You can see there are 7 text values within this Products column.

Let’s see another formula. This time we will use the COUNTIF function.

COUNTIF counts the cells that match the given criteria. We will provide the criteria in such a way so that it counts only text strings.

Let’s write the generic formula first

COUNTIF(range,"*")

COUNTIF - How Do I Count Cells with Text in Excel

You might have already understood the range parameter here. You need to insert the range where you want to operate.

Using " ", we are telling Excel that the criteria should be a text. And * is the wildcard, that says any letter can be in the text.

Write the formula in Excel.

COUNTIF result - How Do I Count Cells with Text in Excel

Let’s see another formula using the COUNT function. We will use IF and ISTEXT alongside the COUNT function.

The generic formula will be like something like this

COUNT(IF(ISTEXT(range),1))

IF-ISTEXT - How Do I Count Cells with Text in Excel

ISTEXT returns an array of TRUE or FALSE. For every text value within the range it returns TRUE and otherwise FALSE.

Inside the IF function, we are creating logic in such a way that for TRUE value it returns 1.

Then the COUNT function will count the number (1s).

Write the formula in Excel.

IF-ISTEXT result-How Do I Count Cells with Text in Excel

1.2. Count using SUM functions

In this section, we will see how to count text cells using the SUM function family.

Let’s see the generic formula first

SUM(IF(ISTEXT(range),1))

SUM - How Do I Count Cells with Text in Excel

Hope you have understood the mechanism of IF and ISTEXT function as it is similar to what we have discussed in the earlier section. It provides 1 for TRUE values.

The SUM function will sum them up. Write the formula in Excel.

SUM result - Hope you have understood the mechanism of IF and ISTEXT function as it is similar to what we have discussed in the earlier section. It provides 1 for TRUE values. The SUM function will sum them up. Write the formula in Excel.

Instead of the SUM function, we can use the SUMPRODUCT function in this formula.

SUMPRODUCT(IF(ISTEXT(range),1))

SUMPRODUCT - How Do I Count Cells with Text in Excel

The inside mechanism of this formula is the same as previous, just the SUMPRODUCT function operates better for arrays.

Write the formula in Excel. Since it is an array formula, use CTRL + SHIFT + ENTER in order to run this formula (only hitting ENTER will not work properly).

SUMPRODUCT result - How Do I Count Cells with Text in Excel

Let’s get rid of this IF function from the formula.

If we simply exclude the IF function from the formula then we will not end up with the correct result.

Let’s write the ISTEXT portion of the previous formula.

ISTEXT-How Do I Count Cells with Text in Excel

And press F9 key.

ISTEXT array - How Do I Count Cells with Text in Excel

It returns an array of TRUE and FALSE.

How can we count the sum of these Boolean values? To resolve this issue, we need to convert these TRUE and FALSE into numbers. We can do that by inserting two dash or hyphens (--) in front of ISTEXT. 

So, our generic formula will be

SUMPRODUCT(--(ISTEXT(range))

SUM -ISTEXT - How Do I Count Cells with Text in Excel

The '--' in front of the ISTEXT function converts the array of 1 and 0. Then SUMPRODUCT function makes the sum and returns the total.

Write the formula in Excel.

SUM-ISTEXT result - How Do I Count Cells with Text in Excel

Instead of this ‘double dash’ you can use another function called SIGN. 

SUMPRODUCT(SIGN(ISTEXT(range)))

SIGN - How Do I Count Cells with Text in Excel

The SIGN function does the exact same exact thing as the ‘double dash’. We are leaving Microsoft Support site link for knowing about the SING function more.

SUMPRODUCT - sign - result - How Do I Count Cells with Text in Excel

Remember all these formulas can be written using SUM as well.

2. Count Text Excluding Spaces/Empty string

In our example, we have an empty row with the Products column. None of our previous formulas counted that cell.

But instead of leaving that cell blank if we had entered Spaces (by mistake)

Space - How Do I Count Cells with Text in Excel

Our formulas would have counted that cell as a text cell as well.

To resolve this issue, we can use the below formula

COUNTIF(range,"><")

Space eliminator ><- How Do I Count Cells with Text in Excel

Using "><" we are setting things in a way that it will look for any value apart from space.
Write the formula in Excel.

>< result - How Do I Count Cells with Text in Excel

It worked fine. Didn’t count the empty space as value.

We can write the formula using the COUNTIFS function. This function counts based on multiple criterias.

COUNTIFS(range,"*",range,"><")

Space eliminator 2 - How Do I Count Cells with Text in Excel

Using "*" we are matching text and "><" excludes any cell that only contains space.

Space Eliminator 2 result - How Do I Count Cells with Text in Excel

3. Count Specific text within a string

If you need to count any specific string within the text all you need to do is provide that string as criteria in the formula.

Our generic formula will be something like

COUNTIF(range,"string")

Specific text - How Do I Count Cells with Text in Excel

Here, for example, we are using Apples as our specific string. You might have noticed we have used “*Apples*”. Adding * either side of the string allows us to search the string regardless of where it stands within the text.

Specific result - How Do I Count Cells with Text in Excel

Here we have two Apples inside the Products column.

We can count specific strings using SUMPRODUCT as well. We need to use ISNUMBER and FIND functions with SUMPRODUCT to do that.

Our generic formula will be like

SUMPRODUCT(--ISNUMBER(FIND("string",range)))

Specific string - How Do I Count Cells with Text in Excel

Using the FIND function we will find an array of the count of the string within the range.

The ISNUMBER will check whether the array has a number or not and will return an array of TRUE or FALSE. 

Double dash in front of ISNUMBER will convert the array into an array of 1 and 0.

Then SUMPRODUCT will return the total.

Write the formula in excel.

Specific cells - How Do I Count Cells with Text in Excel

4. Count Text in Multiple Columns

To count text in multiple columns you need to input the columns as the range in the formula.

COUNTIF(multiple column range,"*")

Multiple columns - How Do I Count Cells with Text in Excel

Here we have inserted the Quantity column as well with the Products column. Write the formula in Excel.

Multiple cells - How Do I Count Cells with Text in Excel

5. Count Text for Filtered Cells

For filtered cells, if we write any of the previous formulas it may not work as we desire.

Non filtered - How Do I Count Cells with Text in Excel

Here we have written the COUNTIF formula for Products. It is showing total text cells within this column.

Now if we filter the table, then our total number of items will be the same.

Filtered wrong value - How Do I Count Cells with Text in Excel

But we need to see how many text cells are there within our filtered result.

We will use the formula below

SUMPRODUCT(SUBTOTAL(103, INDIRECT("Cell"&ROW(range))), --(ISTEXT(range)))

INDIRECT -How Do I Count Cells with Text in Excel

The ISTEXT function checks each cell in range and returns TRUE if a cell contains text, FALSE otherwise. The double dash operator (–) coerces the TRUE and FALSE values into 1’s and 0’s.

INDIRECT function to set the individual references of all cells in the specified range to.

We have a ROW function inside the INDIRECT function. We have written “A” and inside the ROW function given the range. This will give an array of entire values within these cells.

The SUBTOTAL function returns an array of 1’s and 0’s where 1s represent visible cells and 0s match hidden cells.

To identify all cells regardless of whether they are hidden or filtered out, we need to insert the 103 value.

SUMPRODUCT finds two arrays, produced by SUBTOTAL and ISTEXT, it multiplies two arrays and then calculates the sum for the value.

Write the formula in Excel.

INDIRECT result - How Do I Count Cells with Text in Excel

We have yet to utilize the filter here, so it’s giving 8 as a result. Filter the values.

We have filtered for values that are less than 15 in quantity.

INDIRECT Filtered -How Do I Count Cells with Text in Excel

There are 3 cells visible, and our formula shows us that as result.

Previously we told you within SUMPRODUCT two arrays get multiplied. We can multiply them earlier. Our next formula will be on the basis of that.

SUMPRODUCT(SUBTOTAL(103, INDIRECT("Cell"&ROW(range)))*(ISTEXT(range)))

INDIRECT array product - How Do I Count Cells with Text in Excel

Here we are multiplying two arrays produced by SUBTOTAL and ISTEXT. 

SUBTOTAL returns an array of 1 and 0, ISTEXT returns array of TRUE and FALSE. 

You may wonder how we can multiply them?

While multiplying Excel counts TRUE as 1 and FALSE as 0. So, for Excel, it’s nothing but two arrays of 1s and 0s.

Write the formula in Excel.

INDIRECT product result - How Do I Count Cells with Text in Excel

Use filters. This time we have used filters in Products column.

Filter indirect product - How Do I Count Cells with Text in Excel

We can write the formula using the OFFSET function as well.

SUMPRODUCT(SUBTOTAL(103, OFFSET(range, ROW(range) - MIN(ROW(range)),,1)), -- (ISTEXT(range)))

OFFSET - How Do I Count Cells with Text in Excel

What we have done inside the INDIRECT function, our intention was same while using the OFFSET function. We found array of entire column value within the OFFSET function.

If you have any doubt about the OFFSET function, you can visit Microsoft Support site to know more about it.

Writing the formula in Excel use filters.

OFFSET filtered - How Do I Count Cells with Text in Excel

For this OFFSET formula as well you can multiply the two arrays prior to moving to the SUBPRODUCT section. Can you do that on your own? Write on the comment section.

Conclusion

That’s all for today. We have tried listing several ways to count text cells in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know other methods that we might have missed here.

shakil

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo