## Table of Contents

- INTRODUCTION
- What is the use of COUNTIF FUNCTION in Excel?
- PREREQUISITES TO LEARN COUNTIF FUNCTION
- What is the syntax formula of COUNTIF FUNCTION in Excel?
- Example on Countif Function in Excel
- ADDITIONAL WAYS TO USE COUNTIF FUNCTION IN EXCEL

## INTRODUCTION

*In this article, we’ll learn about the countif function in Excel. COUNTIF FUNCTION comes under the STATISTICAL FUNCTIONS category in Excel.*

*It is an extension to the COUNT FUNCTION.*

*COUNTIF function , as the name suggests, does the counting job in Excel , just like the COUNT FUNCTION with the difference that we can provide condition in COUNTIF FUNCTION which cells to count.*

*The other difference is that it works independently of the type of data which means that it would work for numbers as well as text or any other data type.*

*COUNTIF FUNCTION is very helpful when we need to use any nested functions to make a check on the number of cells containing the number or number type of data.*

*This function is also very helpful when we are programming VBA. We’ll be needing a lot of functions to know the status of the sheet so that we can decide what to do further. This function is one of those functions which are helpful.*

*Let us learn ,how to use this function.*

## What is the use of COUNTIF FUNCTION in Excel?

**COUNTIF FUNCTION returns the total number of cells from a given data set or range which fulfill a particular given criteria.**

COUNTIF FUNCTION is apt for the situation when we want to count the number of cells which contains any specific condition or any word from the given set of cells.

For example, suppose we have a large number of cells containing different words. Now if we want to find out the number of cells containing any specific word, we can do that with the help of this function.

This function is a must to learn because we encounter such situations frequently while using excel.

## PREREQUISITES TO LEARN COUNTIF FUNCTION

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in Excel.
- Some information about the STATISTICAL terms is an advantage for the use of such formulas.
- Of course, Excel software.

Helpful links for the prerequisites mentioned above

What Excel does? How to use formula in Excel?

## What is the syntax formula of COUNTIF FUNCTION in Excel?

**The Syntax [ the way the function is used ] for the function is**

**=COUNTIF(RANGE, CRITERIA)**

**RANGE Given range or group of cells which needs to be scanned to find out the number of cells which fulfill the criteria**

**CRITERIA The criteria are the conditions on the basis of which , cells will be counted.**

**NOTE* CRITERIA NEEDS TO BE GIVEN IN THE “”.** For example “=45” or “gyankosh” etc.

## Example on Countif Function in Excel

### DATA SAMPLE

Let us take a mixed data consisting of numbers, dates, numbers as text and text etc. so that we can check the working of function COUNTIF by giving different criteria.

The data used is shown below.

12 |

134 |

234 |

LONDON |

34 |

PARIS |

34 |

DELHI |

234 |

OSLO |

2 |

OTAWA |

234 |

34 |

2 |

TOKYO |

234 |

We’ll find out the cells which has value more than 100

and

The cells which has value more than 100 and less than 200.

And finally using a wildcard will find the place which has “don” at the end of its name and three letters before “don”.

**Follow the steps to find out the different results using countif function**

STEPS:

1. Place the cursor in the cell and type the following function.

**CRITERIA 1: >100**

**=COUNTIF(C5:C21,”>100″)**

2. Press ENTER, the answer will appear as 9.

3. Place the cursor in the cell and type the following function

**CRITERIA 2: >100 AND <200**

**=COUNTIF(C5:C21,”>100″)-COUNTIF(C5:C21,”>200″)**

*If there are two conditions, its better to make a logic like the one we made. It fulfills the conditions but the condition is single.

4. Press ENTER, the answer will appear as 1.

5. Place the cursor in the cell and type the following function

**CRITERIA 3: PLACES ENDING WITH “DON”**

**=COUNTIF(C5:C21,”???DON”)**

6. Press ENTER, the answer will appear as 1.

The answers can be checked manually.

We find that all answers are correct.

The following animation shows all the functions used.

### GENERALIZED STEPS TO USE COUNTIF FUNCTION

HERE ARE THE STEPS TO USE COUNT FUNCTION

- Type the function
- =COUNTIF(RANGE, CRITERIA) in the cell where we want the result.
- Press ENTER and the result will appear.

## ADDITIONAL WAYS TO USE COUNTIF FUNCTION IN EXCEL

Example 1: Counting specific values using Countif function in Excel

If you have a list of fruits in cells A1 to A10 and you want to count how many times “Apple” appears:

`=COUNTIF(A1:A10, "Apple")`

Example 2: Counting based on a number condition using Countif Function

If you want to count how many values in cells B1:B10 are greater than 50:

`=COUNTIF(B1:B10, ">50")`

### Example 3: Counting based on a text condition using Countif Function

To count cells that contain the word “Pass” in a range C1:C10:

`=COUNTIF(C1:C10, "Pass")`

Example 4: Counting based on wildcard characters using countif function in Excel

You can also use wildcards with `COUNTIF`

. For example, to count cells that begin with “A”:

**=COUNTIF(A1:A10, "A*")**

`*`

matches any sequence of characters.`?`

matches any single character.

### Notes:

- The function is case-insensitive (i.e., “Apple” and “apple” are treated the same).
- You can only apply a single condition with
`COUNTIF`

. If you need multiple conditions, you can use`COUNTIFS`

.