How to Count Cells with Specific Text in Excel (Both Case Sensitive and Insensitive)

COUNTIF() with Asterisk in Excel

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.

A Data Set in Excel


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.

COUNTIF Function in Excel

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*”)

COUNTIF Function in Excel with Asterisk

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.

COUNTIF in Excel with Asterisk


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

COUNTIF() Function with Aterisk

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)

COUNTIF() in Excel with Asterisk


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

COUNTIF() Function in Excel

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&”*”)

COUNTIF() with Asterisk in Excel

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

SUMPRODUCT and EXACT function in Excel

  • 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 to 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.


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

SUMPRODUCT, ISNUMBER and FIND Function in Excel

  • 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 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″)

COUNTIFS() Function in Excel

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.


Further Readings

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo