# How to Count Cells with Specific Text in Excel (Both Case Sensitive and Insensitive) While working in Excel, we often have to count cells with some specific text. Today I will show some easy ways to achieve that.

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

#### 1.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.

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.

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 small case and some in upper case. But the COUNTIF() function detected both and gave the total number as 5.

#### 1.2 Matching Partial Cell

##### 1.2.1 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. ##### 1.2.2 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 cell reference in place of the specific text, use this formula

=COUNTIF(E4:E15,”*”&G4) ##### 1.2.3 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

#### 2.1 Matching Complete Cell

##### 2.1.1 Using  a combination of SUMPRODUCT() and 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.

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)) • EXACT(“Leo Tolstoy”,C4:C15) returns a sequence of Boolean values, TRUE and FALSE.
• “–” converts the Boolean values into 1 and 0. 1 for TRUE and 0 for FALSE.
• SUMPRODUCT(–EXACT(“Leo Tolstoy”,C4:C15)) returns the sum of the 1’s and 0’s. This is the number of times Leo Tolstoy is exactly belonging in the Authors’ list.

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.

#### 2.2 Matching Partial Cell

##### 2.2.1 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 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 error if it does not match.

ISNUMBER() Function

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

So, the formula that we will use is

=SUMPRODUCT(–ISNUMBER(FIND(“Bronte”,C4:C15))) • 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.
• ISNUMBER(FIND(“Bronte”,C4:C15)) converts the numbers into TRUE and the errors into FALSE.
• 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 of Bronte sisters is 4.

### 3. Matching Multiple Criteria

#### Using COUNTIFS() Function

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.

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. #### Rifat Hassan

We will be happy to hear your thoughts 