## How to count cells with text in Excel

Excel is everywhere. As a helpful and powerful tool for data analysis and documentation, we often use it in work and life. In some cases, we may need to better understand our data to perform data analysis. In this tutorial, we will demonstrate multiple ways to **count cells with text** in Excel.

**COUNTIF/COUNTIFS function + Wildcard character to count cells with text in Excel **

**COUNTIF function to count cells with text****COUNTIF function count cells with no text****COUNTIFS function to count cells with text excluding visually blank cells**

**SUMPRODUCT + ISTEXT functions to count cells with text in Excel**

**COUNTIF function to count cells containing specific text in Excel**

**Count cells containing certain text with an exact match****Count cells containing certain text with a partial match**

**Easily count cells containing specific text with Kutools for Excel**

**Easily count cells containing specific text with Kutools for Excel****Count and select cells that partially match specific text with Kutools**

** COUNTIF/COUNTIFS function + Wildcard character to count cells with text in Excel **

##### COUNTIF function to count cells with any text

To count cells containing any text, we use** the asterisk symbol (*)** in the COUNTIF formula. The **syntax** of the **COUNTIF function** is as follows:

**=COUNTIF(range, "*")**

Because the asterisk (*) is a wildcard that matches any sequence of characters, the formula counts all cells that contain any text.

For example, to count **cells with text** in the range A2:A15 as shown below, type the below formula into a blank cell, and press the **Enter** button to get the result.

**=COUNTIF(A2:A15, "*")**

**What is not counted as text:**

- Cells with any text;
- Special characters;
- Numbers formatted as text;
- Visually blank cells that contain an empty string (=""), apostrophe (‘), space.

**What is not counted as text:**

- Numbers;
- Dates;
- Logical values of True and False;
- Errors;
- Blank cells.

In this case, we can clearly understand why there are** 8** cells with text in the example worksheet.

##### COUNTIF function count cells with no text

To count cells that do not contain text in a data range, use the **not equal to the logical operator (<>)** and **asterisk symbol (*)** in the COUNTIF formula. The syntax is as follows:

**=COUNTIF(range, "<>*")**

Because the not equal to the logical operator (<>) and asterisk symbol (*) means not equal to any characters, the formula counts the cells without any text.

For example, to **count cells with no text** in the range A2:A15, type the below formula into a blank cell, and press the **Enter** button to get the result.

**=COUNTIF(A2:A15,"<>*")**

Then you get the number of cells that contain non-text values.

##### COUNTIFS function to count cells with text excluding visually blank cells

To count cells with text that excludes visually blank cells, use the asterisk symbol (*), question mark (?), and the not equal to (<>) logical operator in the formula. The syntax is as follows:

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

A **question mark symbol (?)** matches **any single character**, and **an asterisk symbol (*)** matches **any sequence of characters**. A **question mark surrounded by asterisks (*?*)** means that **at least one character should be in the cell**, so the empty string and apostrophe won’t be counted.

The **not equal symbol (<>) plus one empty space** means **not to count the cells with the space character**.

For example, to count cells with text without visually blank cells in the range A2:A15, type the below formula into a blank cell, and press the **Enter** button to get the result.

**=COUNTIFS(A2:A15,"*?*", A2:A15, "<> ")**

Then you can get the number of cells that **only contain visible text**.

- The COUNTIFS function supports multiple conditions. In the example above, to count cells with text, and also exclude cells that contain only one space, empty string, and apostrophe, which all make the cells look blank.
- If you want to count cells with text, and not to count cells that contain only one space, the syntax becomes
**= COUNTIFS(range,"*", range, "<> ")**.

**SUMPRODUCT + ISTEXT functions to count cells with text in Excel**

The second way to count cells with text values is to use the **SUMPRODUCT function** together with the **ISTEXT function**. The **syntax** is as follows:

**=SUMPRODUCT(--ISTEXT(range))**

**=SUMPRODUCT(ISTEXT(range)*1)**

The **ISTEXT function** returns **TRUE or FALSE** when a cell contains text or non-text. And **the double negative (--)** in the first syntax and **the multiplication operation** in the second syntax are two ways to **coerce TRUE and FALSE into the numeric 1 and 0**.

Then** SUMPRODUCT function** returns **the sum of all the ones and zeros** in the search range and **gives a final count**.

In this case, to count cells with text in the range A2:A15, type the below formula into a blank cell, and press the **Enter** button to get the result.

**=SUMPRODUCT(--ISTEXT(A2:A15))**

**=SUMPRODUCT(ISTEXT(A2:A15)*1)**

No matter which syntax you use, the returned result will be **8**.

**COUNTIF function to count cells containing specific text in Excel**

##### Count cells containing certain text with an exact match

To perform an **exact match** of the COUNTIF function, just enter the full text with quotation marks in the second argument in the formula. Here is the **syntax**:

**=COUNTIF(range, "text value")**

To add text in a formula in Excel, **enclosing the text with quotation marks** (“…”) is necessary.

For example, you want to find how many cells in the range A2:A10 contain exactly the word “pen” or “pencil”, type the below formula into a blank cell, and press the **Enter** button to get the result.

**=COUNTIF(A2:A10, "pen")**

**=COUNTIF(A2:A10, "pencil")**

##### Count cells containing certain text with a partial match

To count cells with a **partial match**, place the text between two asterisks (*) and enclose them with quotation marks (“”). Then it allows COUNTIF to **count all the cells containing the text and anything before and after it**. Here is the** syntax**:

**=COUNTIF(range, "*text value*")**

In this case, suppose you want to count how many cells contain “pen” or “pencil” as part of their contents in any position, type the below formula into a blank cell, and press the **Enter** button to get the result.

**=COUNTIF(A2:A10, "*pen*") **

**=COUNTIF(A2:A10, "*pencil*")**

√ Notes: COUNTIF is not case-sensitive.

** Easily count cells containing specific text with Kutools for Excel **

Besides using a formula to count the cells with text, you can use the **Select Specific Cells** utility of **Kutools for Excel **to quickly count and select the cells with specific text in Excel**. **

No matter you want to count cells containing certain text with an **exact match** or with a **partial match**, our Kutools for Excel can help you reach your goal easily.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |

After **free installing ** **Kutools for Excel**, let's have a look at how it works!

##### Count and select cells that exactly match specific text with Kutools

In this case, you want to find how many cells in the range A2:A10 contain exactly the text “pen”.

1. Select the range you want to count the cells containing specific text from.

2. Click **Kutools** > **Select** > **Select Specific Cells**.

3. In the **Select Specific Cells dialog box**, you need to:

- Select
**Cell option**in the**Selection type section**; - In the Specific type section, select
**Equals**in the drop-down list, enter**pen**in the text box; - Click the
**OK**button. Then a**prompt box**pops up to tell you how many cells match the condition.

- Click the
**OK**button in the prompt box and all qualified cells are selected in Excel at the same time.

##### Count and select cells that partially match specific text with Kutools

Suppose you want to count how many cells in the range A2:A10 contain the word "pen" as part of the contents in any position. The trick is similar to the last one.

1. Select the range you want to count the number of cells containing specific text.

2. Click **Kutools** > **Select** > **Select Specific Cells**.

3. In the **Select Specific Cells dialog box**, you need to:

- Select
**Cell option**in the**Selection type section**; - In the Specific type section, select
**Contains**in the drop-down list,**enter pen**in the text box; - Click the
**OK**button. Then a**prompt box**pops up to tell you how many cells match the condition.

- Click the
**OK**button in the prompt box and all qualified cells are selected in Excel at the same time.

## The Best Office Productivity Tools

### Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...

**Enable tabbed editing and reading in Word, Excel, PowerPoint**, Publisher, Access, Visio and Project.- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!