While working in **Microsoft** **Excel**, we often have to count cells with some specific text. Today I will show you **how to count cells with specific text** in **Excel**.

## Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.

## 5 Easy Ways to Count Cells with Specific Text in Excel

In this article, you will learn how to count cells with specific text in **Excel **by using* the COUNTIF function*, combining

*and*

**the SUMPRODUCT function***, and combining*

**the EXACT function****the SUMPRODUCT function**,

*, and*

**the ISNUMBER function***. Let us look at the data set. We have records of various books from a bookstore named Kingfisher Bookstore.*

**the FIND function**### 1. Using COUNTIF Function to Count Complete Cell in Excel

We want to find out how many biographical novels there are. We have to match the complete cells of the column **Book Type.**

**COUNTIF() Function**

- It takes two arguments, the range of cells and one specific criterion.
- Gives the number of cells that match the specific criterion within that range of cells as output.

**Step 1:**

- Firstly, choose the
cell.*C18* - Secondly, type the following formula below here.

`=COUNTIF(E5:E16,"Biographical Novel")`

- Then, press
**ENTER**.

**Step 2:**

- Finally, the given image displays the number of
and the value is*Biological Novels***5.**

**Read More:** **How to Count Number of Cells with Dates in Excel (6 Ways)**

### 2. Utilizing COUNTIF Function to Count Partial Cells with Specific Text in Excel

Here, we will determine the number of cells with specific text for partial cells at any of the positions. Here is our data set where we will apply** the COUNTIF function** to determine the number of cells with specific text for different positions.

#### 2.1.Partial Cell at Beginning

Here, we want to find out all the ** Book Types **starting with

**“historical”.**

**Step 1:**

- Firstly, choose the
cell.*C18* - Then, write down the following formula below here.

`=COUNTIF(E5:E16,"Historical*")`

- Then, hit
**ENTER**.

**Step 2:**

- Lastly, the given image displays the number of book types starting with
and There are*Historical***3**Book Types starting with the text “”.*Historical*

#### 2.2.Partial Cell at End

Now, we want to find all the ** Book Types** ending with

**“**

*Novel*“.**Step 1:**

- Firstly, choose the
cell.*C18* - Then, write down the following formula below here.

`=COUNTIF(E5:E16,"*Novel")`

- Then, hit
**ENTER**.

** Step 2:**

- Lastly, the provided picture shows how many different book categories end in “
**Novel**.” So, there are in total**11 novels.**

#### 2.3.Partial Cell in Middle

In this section, we want to find all the** Book Types** with “**cal”** in the middle.

**Step 1:**

- Firstly, choose the
cell.*C18* - Then, write down the following formula below here.

`=COUNTIF(E5:E16,"*cal*")`

- Then, hit
**ENTER**.

**Step 2:**

- As a result, you will see that there are
**9**Book Types with “**cal”**in the middle.

**Limitations of COUNTIF() Function**

**COUNTIF()**function can not count correctly if the specific text contains more than or close to 255 characters.- It raises a
**#Value Error**if you take a range of cells from another workbook as its argument, and the workbook is closed.

**Read More: ****How to Count If Cell Contains Number (Easiest 7 Ways)**

**Similar Readings**

**Count Empty Cells in Excel (4 Ways)****How to Count Cells That are Not Blank in Excel (8 Useful Methods)****Excel Count Cells with Numbers (5 Simple Ways)****How to Count Filled Cells in Excel (5 Quick Ways)**

### 3. Combining SUMPRODUCT and EXACT Functions to Count Complete Cell

In this part, we will demonstrate to you how to count complete cells with specific text in Excel by combining **the SUMPRODUCT function** and **the EXACT function**.

**SUMPRODUCT() Function**

- Takes a range of numbers or cells as input.
- Gives their mathematical sum as output.

**Exact() Function**

- Takes two inputs, a specific text and a range of cells.
- Returns Boolean values
**, True**if the text matches completely with the cell, and**False**if it does not match.

**Step 1:**

- Firstly, choose the
cell.*C18* - After that, type the following formula below here.

`=SUMPRODUCT(--EXACT("Leo Tolstoy",C5:C16))`

- Then, press
**ENTER**.

**Formula Breakdown**

**EXACT(“Leo Tolstoy”,C4:C15):**This function act as an argument in**the SUMPRODUCT function**which returns a sequence of Boolean values,**TRUE**and**“–”:**This symbo**l**converts the Boolean values into 1 and 0. 1 for**TRUE**and 0 for**FALSE**.**SUMPRODUCT(–EXACT(“Leo Tolstoy”,C4:C15)):**This function returns the sum of the 1’s and 0’s. This is the number of times Leo Tolstoy is exactly belonging to the Authors’ list.

**Step 2:**

- So, we find there are
**3**books written by**Leo Tolstoy**.

**Read More:** **Excel Formula to Count Cells with Text (All Criteria Included)**

### 4. Combining SUMPRODUCT, ISNUMBER, and FIND Functions to Count Partial Cell

In this section, we shall find out how many books have been written by the ** Bronte **sisters. That means either by

**or by**

*Emily Bronte***. We will just match the text**

*Charlotte Bronte***“Bronte”**partially with column

**C**.

**FIND() Function**

- It takes two inputs. One specific text and a range of cells.
- Returns the position of the text in a cell if it matches partially with any cell (case sensitive) and returns an error if it does not match.

**ISNUMBER() Function**

- Takes the output returned by
**the FIND()****function**as input. - Converts the numbers as TRUE and errors as FALSE.

**Step 1:**

- Firstly, choose the
cell.*C18* - After that, type the following formula below here.

`=SUMPRODUCT(--ISNUMBER(FIND("Bronte",C5:C16)))`

- Then, hit
**ENTER**.

**Formula Breakdown**

**FIND(“Bronte”,C5:C16):**This function returns the position of the text “” in the cells of column**Bronte****C**, if it finds any, otherwise returns an error.**ISNUMBER(FIND(“Bronte”,C5:C16)):**This function converts the numbers into**TRUE**and the errors into**FALSE**.- The
**“–”**sign converts the**TRUE**and**FALSE**into**1**and**0**. **SUMPRODUCT(–ISNUMBER(FIND(“Bronte”,C5:C16))):**The**function**gives the sum of all the**0**’s and**1**’s. This is the number of times the word “” is found in the Authors’ list.**Bronte**

**Step 2:**

- So, we find the total number of books available for the Bronte sisters is
**4**.

**Read More:** **Excel Count Number of Cells in Range (6 Easy Ways)**

### 5. Using COUNTIF to Count Specific Text for Multiple Criteria in Excel

Now we go to something a bit more complex. We want to find out the total number of books written by **Leo Tolstoy** but published after the year **1870.**

We will use Excel’s **COUNTIFS() **function here.

**COUNTIFS() Function**

- Takes more than one range of cells and criteria as input.
- Returns the number of times when all the criteria are fulfilled.

**Step 1:**

- Firstly, select the
cell.*C18* - After that, write down the following formula below here.

`=COUNTIFS(C5:C16,"Leo Tolstoy",D5:D16,">1870")`

- Then, press
**ENTER**.

**Step 2:**

- Here
**COUNTIFS()**takes two ranges of cells and two criteria as input. - It finds
**“Leo Tolstoy”**between cellsto*C5*and finds years greater than 1870 from cells*C16***D5**to. Then returns the common number as output.*D16* - Finally, we see the number of books written by
**Leo Tolstoy**published after**1870**is**1**.

**Read More:** **How to Count Filtered Rows with Criteria in Excel (5 Easy Ways)**

## Conclusion

In this article, we’ve covered 5 ways to count cells with specific text in Excel. I sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, **ExcelDemy**. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.