Learn Excel Countif Text connection from basic to advanced

INTRODUCTION
HOW TEXT IS HANDLED IN EXCEL?
BASICS OF EXCEL COUNTIF TEXT CRITERIA
EXAMPLE 1: COUNTING THE NUMBER OF REPETITIONS OF A PARTICULAR TEXT USING EXCEL COUNTIF TEXT CRITERIA
EXAMPLE 2: COUNTING THE RESEMBLING WORDS USING EXCEL COUNTIF TEXT CRITERIA

INTRODUCTION

In this article, we’ll learn about the Excel countif text criteria i.e. using the COUNTIF FUNCTION with text values in Excel.

Countif is a very versatile function which can be used carry out a number of difficult tasks within a fraction of seconds. But we must know the way to make use of this function in the useful ways.

You can learn COUNTIF FUNCTION HERE which is recommended to get a better grasp of the trick being discussed here.

We make use of excel countif text criteria to compare the text values and make a count. The conditions can be many such as any text equal to or containing some portion or substring of other text etc.

LEARN MANY TRICKS WITH GYANKOSH.NET@ MANIPULATING TEXT IN EXCEL – PART I

HERE, WE WOULD LEARN ABOUT THE USAGE OF COUNTIF FUNCTION WITH TEXT TO GET SOME EXCITING RESULTS.

HOW TEXT IS HANDLED IN EXCEL?

TEXT is simply the group of characters and strings of characters that convey the information about the different data and numbers in Excel. Every character is connected with a code [ANSI].

Text comprises of the individual entity character which is the smallest bit which would be found in Excel.We can perform the operations on the strings[Text] or the characters. Characters are not limited to A to Z or a to z but many symbols are also included in this which we would see in the later part of the article.

TEXT IS AN INACTIVE NUMBER TYPE[FORMAT] IN EXCEL. ANYTHING STORED AS TEXT [NUMBER OR DATE] WON’T RESPOND TO ANY STANDARD FORMULAS OR FUNCTIONS BUT SPECIALLY DESIGNED TEXT FUNCTIONS. [EXCEPTIONS DO OCCUR IN CASE OF NUMBERS]

If we need to make anything inactive, such as Date to be nonresponding to the calculation, we put it as a text. Similarly if we want to avoid any calculations for a number it needs to be put as a text.

BASICS OF EXCEL COUNTIF TEXT CRITERIA

COUNTIF FUNCTION counts the values which fulfill the given conditions.

For example, count the numbers from a given data which is smaller than a particular value, or larger than a fixed value or equal to any value etc.

In this section we would focus upon the cases where the conditions will be put on the text rather than numerical values.

Let us learn the excel countif text criteria with the help of few examples.

EXAMPLE 1: COUNTING THE NUMBER OF REPETITIONS OF A PARTICULAR TEXT USING EXCEL COUNTIF TEXT CRITERIA

Let us take a data and find out the repetition of a particular word within the given data.

The data is as given below. These are the names of the places. Some of them are repeated.

WASHINGTON

PARIS

DELHI

WASHINGTON

MOSCOW

ROME

MOSCOW

STEPS TO FIND THE NUMBER OF REPETITIONS OF A PARTICULAR TEXT (“WASHINGTON”)

Select the cell where you want the result.
Put the function as =COUNTIF(RANGE,”TEXT”)

For our example, the formula will be

=COUNTIF(F3:F11,G3) [Check picture below for cell references ]

for the first example where we are trying to find out the number of times WASHINGTON is repeated.

The first argument is the array reference which covers all the data in which we have to find out the repetitions ].

The second argument is the condition that we want to find. In our case, we have put the reference of the text we want to find.

We can also put “WASHINGTON” directly.

THE COMPARISON OF TEXT VALUE IN THE COUNTIF FUNCTION IGNORES THE CASE AND TREATS UPPER AND LOWER CASE AS EQUALS.

We extended the example to find out the number of repetitions of all the given places names.

EXAMPLE 2: COUNTING THE RESEMBLING WORDS USING EXCEL COUNTIF TEXT CRITERIA

Suppose we have the following group of words and we need to find out the resembling words.

A word can resemble in many ways.

Many words can have same prefixes, same suffixes, any pattern

Let us try different combinations.

GENERAL FORMULA TO COUNT THE WORDS HAVING SAME PREFIX:

=COUNTIF(RANGE,”PREFIX*”) PREFIX is the letters of the prefix.

GENERAL FORMULA TO COUNT THE WORDS HAVING SAME SUFFIX:

=COUNTIF(RANGE,”* suffix”) suffix are the letters of the suffix.

GENERAL FORMULA TO COUNT THE WORDS HAVING SAME PATTERN:

=COUNTIF(RANGE,”*pattern*”) pattern is the text pattern.

GENERAL FORMULA TO FIND OUT THE WORD WHICH IS MISSING ONE LETTER.

=COUNTIF(RANGE,”lett?rs*”) The letter at ? will be taken in to consideration and all possibilities will be counted.

Let us try these formulas in the examles.

DATA GIVEN:

KIT

HIT

FIT

LITE

LIGHT

KITE

MITE

MITTEN

KITTEN

SICK

WICK
LET

HOW TO COUNT THE WORDS STARTING WITH THE SAME LETTERS USING EXCEL COUNTIF TEXT CRITERIA?

We can make use of excel countif text criteria to use countif function to count the words starting with the same letters which we also know as prefixes.

Select the cell where you want the result.
Put the function as =COUNTIF(F16:F27,”LI*”)
Click OK.
The answer will appear as 2.

HOW TO COUNT THE WORDS ENDING WITH THE SAME LETTERS USING EXCEL COUNTIF TEXT CRITERIA?

We can make use of excel countif text criteria to use countif function to count the words ending with the same letters which we also know as suffixes.

Select the cell where you want the result.
Put the function as =COUNTIF(F16:F27,”*EN”)
Click OK.
The answer will appear as 2.

HOW TO COUNT THE WORDS CONTAINING THE SAME PATTERNS IN EXCEL?

We can also use countif function to count the words containing similar characters anywhere within the given words.

Select the cell where you want the result.
Put the function as =COUNTIF(F16:F27,”*IT*”)
Click OK.
The answer will appear as 8.

HOW TO USE COUNTIF FUNCTION IN EXCEL TO COUNT WORDS USING WILDCARD CHARACTERS?

Using excel countif text criteria we can also use wildcard characters to count the words fulfilling the condition.

Select the cell where you want the result.
Put the function as =COUNTIF(F16:F27,”L?T”)
Click OK.
The answer will appear as 1.