While working in Excel, we often have to count cells with some specific text. Today I will show some easy ways to achieve that.

## Download File

**How to Count Cells with Specific Text in Excel**

Let us look at the data set. We have the record of various books of a bookstore named Kingfisher Bookstore.

**1. Case Insensitive**

**Case 1: Matching Complete Cell**

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

**Method 1: Using COUNTIF Function**

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

If you want to know more about **COUNTIF()**, visit this link.

Here, the range of cells is **C4:C15**. And the specific criterion is the text “**Biographical novel”**.

So the formula will be

**=COUNTIF(E4:E15,”Biographical novel”)**

Select a cell and write this function there. Then click **Enter.**

Look at the data set again carefully. I have willingly written some Biological novels in a small case and some in upper cases. But the **COUNTIF()** function detected both and gave the total number as 5.

**Case 2: Matching Partial Cell**

**I. Partial Text at the Beginning**

Select a cell and write the formula

**=COUNTIF(range, “text*”).**

Then click** Enter.**

I want to find out all the Book Types starting with **“Historical”.**

So I write the formula

**=COUNTIF(E4:E15,”Historical*”)**

There are 3 Book Types starting with the text “Historical”.

**Note 1: **The **Asterisk(*)** symbol means that there may be any kind of text in its place.

**Note:** If you want to use any cell as the criterion, other than some specific text, for example **G4**, write

**=COUNTIF(E4:E15, G4&”*”).**

The **Ampersand (&) **symbol joins two texts and makes it into one text.

**ii. Partial Text at the End**

Select a cell and write the formula

**=COUNTIF(range,”*text”).**

Then click** Enter.**

I want to find all the Book Types ending with **“Novel”.**

So I write the formula

**=COUNTIF(E4:E15,”*Novel”)**

So, there are in total 11 **novels**. It found out both uppercase and lowercase.

And if you want to use a cell reference in place of the specific text, use this formula

**=COUNTIF(E4:E15,”*”&G4)**

**iii. Partial Text at the Middle**

Select a cell and write the formula

**=COUNTIF(range,”*text*”).**

Then click **Enter. **

I want to find all the** Book Types** with “**cal”** in the middle.

So I write the formula

**=COUNTIF(E4:E15, “*cal*”).**

So there are 9 Book Types with “**cal”** in the middle.

And if you want to use cell reference in place of text, use this formula

**=COUNTIF(E4:E15,”*”&G4&”*”)**

**Limitations of COUNTIF() Function**

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

**2. Case Sensitive**

**Case 1: Matching Complete Cell**

**i. Using a combination of SUMPRODUCT() and EXACT() Functions**

**SUMPRODUCT() Function**

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

If you want to know more about **SUMPRODUCT(),** visit this link.

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

If you want to know more about **EXACT(),** visit this link.

Now, Think for a moment we want to know how many books there are written by Leo Tolstoy.

Select a cell and insert this formula

**=SUMPRODUCT(–EXACT(“Leo Tolstoy”,C4:C15))**

returns a sequence of Boolean values,`EXACT("Leo Tolstoy",C4:C15)`

**TRUE**and**FALSE.****“–”**converts the Boolean values into 1 and 0. 1 for**TRUE**and 0 for**FALSE**.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.`SUMPRODUCT(--EXACT("Leo Tolstoy",C4:C15))`

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

**Note:** If you want to put any cell reference in place of the text in the formula, just put that. The rest is the same.

**Case 2: Matching Partial Cell**

**i. Using a combination of SUMPRODUCT(), ISNUMBER() and FIND() Function**

In this section, we shall find out how many books there are written by the Bronte sisters. That means either by Emily Bronte or by Charlotte Bronte. We will just match the text **“Bronte”** partially with column **C**.

In order to know about the **SUMPRODUCT()** function, go to section 2.1.1

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

If you want to know more about **FIND(),** visit this link.

**ISNUMBER() Function**

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

If you want to know more about **ISNUMBER(),** visit this link.

So, the formula that we will use is

**=SUMPRODUCT(–ISNUMBER(FIND(“Bronte”,C4:C15)))**

returns the position of the text “Bronte” in the cells of column C, if it finds any, otherwise returns an error.`FIND("Bronte",C4:C15)`

converts the numbers into TRUE and the errors into FALSE.`ISNUMBER(FIND("Bronte",C4:C15))`

- The “–” sign converts the TRUE and FALSE into 1 and 0.
- The SUMPRODUCT() gives the sum of all the 0’s and 1’s. This is the number of times the word “Bronte” is found in the Authors’ list.

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

**3. Matching Multiple Criteria**

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.** How can we achieve that?

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.

If you want to know more about **COUNTIFS()**, visit this link.

The formula that we will use here is

**=COUNTIFS(C4:C15,”Leo Tolstoy”,D4:D15,”>1870″)**

Here **COUNTIFS()** takes two ranges of cells and two criteria as input.

It finds** “Leo Tolstoy”** between cells **C4** to **C15** and finds years greater than 1870 from cells** D4** to **D15**. Then returns the common number as output.

So, we see the number of books written by **Leo Tolstoy** published after **1870** is 1.

## Conclusion

Using these methods, you can easily count the number of cells with a specific text in Excel. Do you know any other method? Let us know in the comment section.