How to Count Cells with Specific Text in Excel (5 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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 the SUMPRODUCT function and the EXACT function, and combining the SUMPRODUCT function, the ISNUMBER function, and the FIND function. Let us look at the data set. We have records of various books from a bookstore named Kingfisher Bookstore.

 Easy Ways to Count Cells with Specific Text in Excel


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 C18 cell.
  • Secondly, type the following formula below here.
=COUNTIF(E5:E16,"Biographical Novel")
  • Then, press ENTER.

 Using COUNTIF Function to Count Complete Cells with Specific Text in Excel

Step 2:

  • Finally, the given image displays the number of Biological Novels and the value is 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 C18 cell.
  • Then, write down the following formula below here.
=COUNTIF(E5:E16,"Historical*")
  • Then, hit ENTER.

Partial Cell at Begining to Count Cells with Specific Text in Excel

Step 2:

  • Lastly, the given image displays the number of book types starting with Historical and There are 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 C18 cell.
  • Then, write down the following formula below here.
=COUNTIF(E5:E16,"*Novel")
  • Then, hit ENTER.

Partial Cell at End to Count Cells with Specific Text in Excel

 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 C18 cell.
  • Then, write down the following formula below here.
=COUNTIF(E5:E16,"*cal*")
  • Then, hit ENTER.

Partial Cell in Middle to Count Cells with Specific Text in Excel

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


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 C18 cell.
  • After that, type the following formula below here.
=SUMPRODUCT(--EXACT("Leo Tolstoy",C5:C16))
  • Then, press ENTER.

 Combining SUMPRODUCT and EXACT Functions to Count Complete Cells with Specific Text in Excel

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 symbol 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 Emily Bronte or by Charlotte Bronte. We will just match the text “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 C18 cell.
  • After that, type the following formula below here.
=SUMPRODUCT(--ISNUMBER(FIND("Bronte",C5:C16)))
  • Then, hit ENTER.

Combining SUMPRODUCT, ISNUMBER, and FIND Functions to Count Partial Cell with Specific Text in Excel

Formula Breakdown

  • FIND(“Bronte”,C5:C16): This function returns the position of the text “Bronte” in the cells of column 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 “Bronte” is found in the Authors’ list.

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 C18 cell.
  • After that, write down the following formula below here.
=COUNTIFS(C5:C16,"Leo Tolstoy",D5:D16,">1870")
  • Then, press ENTER.

Using COUNTIFS for Multiple Criteria to Count Cells with Specific Text in Excel

Step 2:

  • Here COUNTIFS() takes two ranges of cells and two criteria as input.
  • It finds “Leo Tolstoy” between cells C5 to C16 and finds years greater than 1870 from cells D5 to D16. Then returns the common number as output.
  • 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.


Related Articles

Rifat Hassan

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo