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

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

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,"*")`

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.

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

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

### 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))`

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

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

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.

And press **F9 **key.

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

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.

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

`SUMPRODUCT(SIGN(ISTEXT(range)))`

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.

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)

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,"><")`

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

Write the formula 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,"><")`

Using `"*"`

we are matching text and `"><"`

excludes any cell that only contains space.

## 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")`

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.

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

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.

## 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,"*")`

Here we have inserted the *Quantity *column as well with the *Products *column. Write the formula 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.

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.

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

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.

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.

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

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.

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

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

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

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.

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.