HOW TO COUNT THE CELLS CONTAINING SPECIFIC TEXT IN EXCEL?

Table of Contents

INTRODUCTION

Counting specific data is a very frequent requirement in Excel in our day to day work.

Count can be required many times while preparing any sheet for a particular purpose.

In this article , let us try to find out the Cells which contain a specific text string, or specific characters in Excel.



PURPOSE

The count of the cells which contains any specific text string or characters can prove very helpful in the cases where we need to make a decision on the basis of the count.

There can be numerous occassions where we might need to count the number of cells containing specific text or string.

The string to be can be in small case or big case or in mixed case.

Let us try to find out the different ways to count the number of cells containing specific text or characters.



EXAMPLE 1: COUNT THE NUMBER OF CELLS WHICH CONTAIN THE TEXT “THE” [EXACT ] IN THE GIVEN DATA

Let us take random data to try out our examples.

DATA
THE
HERE
THERE
WHAT
WHY
WHEN
THEM
WHERE
THEN
THAT
THAN
HI
DATA

Let us discuss the example in various ways.

FOLLOW THE STEPS TO FIND OUT THE CELLS WHICH CONTAINS THE TEXT “THE”

  1. Select the cell where you want the result.
  2. Enter the formula as =COUNTIF(RANGE,CRITERIA).
  3. For our example, use the formula as =COUNTIF(E22:E33,”THE”).
  4. The count of the cells containing the text ” THE ” will appear.

COUNT THE NUMBER OF CELLS CONTAINING “THE”

EXPLANATION:

We have made use of the function COUNTIF which is very useful in counting the number of cells containing any specific text.

The used formula is =COUNTIF(E22:E33,”THE”).

where E22:E33 is the range containing the data and “THE” is the text to be searched for.

The result will search out all the “THE” in the data and return the result. It will search for the THE which matches exactly with the given text except the Case.

COUNTIF FUNCTION DOESN'T CARE ABOUT THE CASE OF THE TEXT TO BE SEARCHED FOR BUT IT'LL TAKE CARE OF THE PATTERN.
LEARN COUNTIF FUNCTION HERE

Extending the example further, let us count the cells which contains the text “THE” within any word or alone. i.e. all the text like THERE, there, them etc. will be counted as these all words conting THE.



EXAMPLE 2: COUNT THE CELLS CONTAINING THE CELLS CONTAINING TEXT “THE” IN Excel

In this example, we’ll try to count the cells which contains the pattern “THE” in the given data.

FOLLOW THE STEPS TO COUNT THE CELLS CONTAINING THE GIVEN TEXT PATTERN”

  1. Select the cell where you want the result.
  2. Enter the formula as =COUNTIF(RANGE,CRITERIA).
  3. For our example, use the formula as =COUNTIF(E43:E54,”*THE*”).
  4. The count of the cells containing all the words containing ” THE ” as a part will appear.
COUNT ALL THE CELLS CONTAINING “THE” WITHIN ANY WORD

EXPLANATION:

We have made use of the function COUNTIF which is very useful in counting the number of cells containing any specific text.

The used formula is =COUNTIF(E43:E54,”*THE*”).

where E43:E54 is the range containing the data and “*THE*” is the text to be searched for.

The * applied in front and after the text patter THE will tell COUNTIF to search for all the possibilities of the presence of the text pattern THE. It means we’ll use the wildcard character * to show the presence of any text at the beginning or end of the text pattern.

The result will search out all the “THE” containing words in all the cells given in the data and return the result.

The result is 4 as THEN, THEM , THERE and THE are the four words which show this pattern.

COUNTIF FUNCTION DOESN'T CARE ABOUT THE CASE OF THE TEXT TO BE SEARCHED FOR BUT IT'LL TAKE CARE OF THE PATTERN.
LEARN COUNTIF FUNCTION HERE

After this, let us try to find out the cells which doesn’t contain any specific text.



EXAMPLE 3: COUNT THE NUMBER OF CELLS THAT DO NOT CONTAIN SPECIFIC TEXT IN Excel

In this example, we’ll count the cells lwhich don’t contain the text “THE”.

FOLLOW THE STEPS TO COUNT THE NUMBER OF CELLS WHICH DON’T CONTAIN “THE”:

  1. Select the cell where you want the result.
  2. Enter the formula as =COUNTIF(RANGE,CRITERIA).
  3. For our example, use the formula as =COUNTIF(E67:E78,”<>THE”).
  4. The count of the cells containing the text ” THE ” will appear.

EXPLANATION:

We have made use of the function COUNTIF which is very useful in counting the number of cells containing any specific text.

The used formula is =COUNTIF(E67:E78,”<>THE”).

where E67:E78 is the range containing the data and “<>THE” is the text to be searched for.

The <> is not equal to operator, which simply tells the functions to compare the data which IS NOT EQUAL TO THE and hence returns the result as 11 as there is only one value THE which is true.

COUNTIF FUNCTION DOESN'T CARE ABOUT THE CASE OF THE TEXT TO BE SEARCHED FOR BUT IT'LL TAKE CARE OF THE PATTERN.
LEARN COUNTIF FUNCTION HERE

Let us finally put case sensitivity in this and count the cells which contains a certain text exactly.



EXAMPLE 4: COUNT THE CELLS CONTAINING A GIVEN TEXT EXACTLY [ CASE SENSITIVE ]

In all the examples discussed earlier, we should know that COUNTIF never care about the case. It means it’ll search THE same as The, the or thE.

What if we want to search for the exact matches.

Well, in that case we can make use of some other functions in addition to our COUNT and get the result.


USING COUNT FUNCTION WITH FIND FUNCTION



FOLLOW THE STEPS TO COUNT THE CELLS CONTAINING GIVEN TEXT EXACTLY:

  1. Select the cell where you want the result.
  2. Enter the formula as =COUNT( IF ( FIND THE TEXT AND PUT RESULT AS 1, OTHERWISE 0 )).
  3. For our example, use the formula as =COUNT(ARRAYFORMULA(IF(FIND(“The”,E84:E95,1)>0,1,0))).
  4. The count of the cells containing the text ” The” will appear.
  5. The result appears as 1 because only one “The” exist in the given data.
WE HAVE CHANGED THE EXAMPLE DATA A BIT FOR THIS EXAMPLE. WE HAVE PUT THREE VERSIONS OF "THE" as the, The and THE SO THAT WE CAN TRY TEST OUR SOLUTION.

EXPLANATION:

We have made use of the function COUNTIF which is very useful in counting the number of cells containing any specific text.

The used formula is =COUNT(ARRAYFORMULA(IF(FIND(“The”,E84:E95,1)>0,1,0))).

where E84:E95 is the range containing the data and “The” is the text to be searched for.

The OUTER function COUNT will count the result returned by the internal formulas.

ARRAYFORMULA will treat the FIND FUNCTION as an Array function and will process the complete range as passed to FIND FUNCTION.

The FIND function with the help of IF FUNCTION will search for “The” one by one in all the cells of the range and will return 1 if “The” is present , otherwise it’ll return a 0.

All the 1s will be counted and the result will be shown to the user.

COUNTIF FUNCTION DOESN'T CARE ABOUT THE CASE OF THE TEXT TO BE SEARCHED FOR BUT IT'LL TAKE CARE OF THE PATTERN.
LEARN COUNTIF FUNCTION HERE

In this way we can count the presence of a given text or pattern in various ways in Excel.