COUNTIF Function in Excel - Step by Step Tutorial

Excel Countif Function - Quick Steps

• Select the Range
• Define the Criteria
• Enter the Formula =COUNTIF(range, criteria)
• Press Enter

Ever needed to quickly count cells that meet specific criteria in Excel? The COUNTIF function in Excel is your go-to tool for this task. Whether you're analyzing data, filtering results, or tracking metrics, COUNTIF helps you make sense of it all. From counting specific text entries to numbers or even dates, its versatility can simplify your workflow.

In this guide, we’ll explore how to use the COUNTIF formula, break down its syntax, and provide practical COUNTIF examples in Excel, including working with multiple criteria. By the end, you’ll have a clear understanding of how to apply this function to enhance your data analysis skills.

What is Countif Function in Excel

The COUNTIF function counts the number of cells in a range that satisfy a given condition. It’s especially useful for filtering and analyzing data based on specific criteria, such as counting cells containing a particular text, value, or range.

COUNTIF Function Syntax

COUNTIF(range, criteria)

• range: The range of cells that you want to apply the criteria to.
• criteria: The condition that determines which cells should be counted. This can be a number, text, expression, or even a cell reference.

COUNTIF Formula Syntax and Parameters

COUNTIF Function is one of the Statistical Functions, to count the number of cells that meet a criterion; for example, to count the number of times a student appears in an attendance list.

The simplest form of COUNTIF Formula is:

=COUNTIF(Where do you want to look?, What do you want to look for?)

Return Value:- COUNTIF function in excel returns an integer value of the number of cells which satisfy the given condition.

How the COUNTIF Function Works

COUNTIF Function works as Follows:

Specifying the Range:

• The range refers to the group of cells you want to check for the condition.
• For example, if you have a list of sales figures in column B, you might want to count how many sales are above $500. Here, the range would be B2:B20.

Setting the Criteria:

• The criteria is the condition you want to apply to count the matching cells.
• For numbers, you can specify a value or a logical operator. For example, ">500" would count all values greater than 500 in the range.
• For text, you can specify an exact match (e.g., "Apple") or use wildcards (e.g., "*Apple" to count cells that contain "Apple" anywhere within the text).

How to Use COUNTIF Function in Excel: Step-by-Step Guide

The COUNTIF function in Excel is used to count the number of cells in a range that meet a specific condition or criterion. It’s especially helpful for quickly analyzing data that fits a particular pattern or requirement.

Step 1: Prepare your Data in MS Excel

Open Excel and enter data into the sheet.

Step 2: Select the Cell to Enter the Formula

Click on the cell where you want to display the result of the COUNTIF function.

Step 3: Start Typing the Formula

Type the following formula into the selected cell:

=COUNTIF(

Step 4: Define the Range and Criteria

The range refers to the group of cells you want to evaluate. For instance, if you're counting occurrences in cells from A1 to A10, the range would be A1:A10. The criteria specify the condition for counting cells, which can be a specific value, text, or expression.

=COUNTIF(Range, Criteria)

Step 5: Press Enter and Preview Result

Now Press Enter to preview your Result.

In the below example, we will understand how to use COUNTIF Function in Excel:

Excel COUNTIF Function Example

You have a list of employees in the table, and you want to count how many of them belong to the Marketing department. You’ll use the COUNTIF function to achieve this.

Step 1: Enter the Data into the Sheet

Open MS Excel and enter the data into the sheet. Here we have entered dataset containing employee details. Additionally, the department names have been listed separately in column H for easy reference when applying formulas.

Step 2: Select the Cell to Input the Formula

Click on the cell where you want the result to appear. Here we have selected I5.

=COUNTIF(

Step 3: Select the Range B2:B13

Highlight the range of cells containing the department names, For example here we have selected, B2:B13.

The formula now looks like:

=COUNTIF(B2:B13,

Step 4: Enter the Criteria and Press Enter

Specify the criteria as "Marketing" (the text you want to count). You can either type it manually in double quotes or reference a cell containing the word. In this case, we have selected H5, which contains "Marketing".

=COUNTIF(B2:B13, "Marketing") 
or
 =COUNTIF(B2:B13, H5)

Step 5: Preview Result

Excel will evaluate the range B2:B13 and count all occurrences of the word "Marketing". The result is displayed in the selected cell (I5).

Excel COUNTIF with Multiple Criteria

COUNTIF Function is not exactly designed to count cells with multiple criteria. The COUNTIFS function is used to count cells that match two or more criteria(AND logic). You can combine two or more COUNTIF functions in one formula to solve some easy tasks.

Count Values between Two Numbers

The most common application of Excel COUNTIF function with 2 criteria is counting numbers within a specific range, i.e. less than X but greater than Y.

Below is a formula that is used to count cells in the range A2:A9 where a value is greater than 5 and less than 15.

=COUNTIF(A2:A9,">5") - COUNTIF(A2:A9,"=15)

Advanced Tips for Using COUNTIF Function in Excel

Below are some advanced Tips for COUNTIF Functions in Excel:

1. Using Wildcard Characters for Partial Matches:

Wildcard characters in COUNTIF allow you to count cells that match partial text. There are two main wildcard characters:

Asterisk (*): Represents any number of characters.

• For example, =COUNTIF(A2:A10, "*apple*") will count cells that contain "apple" anywhere in the text (e.g., "Green apple", "apple pie").

Question mark (?): Represents a single character.

• For example, =COUNTIF(A2:A10, "a?ple") will count cells that contain words like "Apple" or "Aple", where there is exactly one character between "a" and "ple".

2. Count Values Greater Than, Less than or Equal to a Specific Number:

The COUNTIF function in Excel allows you to count cells that meet specific numerical conditions, such as being greater than, less than, or equal to a certain number.

1. Count Values Greater than

To count cells with values greater than a certain number, you can use a comparison operator like >.

Formula: =COUNTIF(A2:A10, ">500")

This will count all cells in the range A2:A10 that have values greater than 500.

2. Count Values Less Than a Specific Number:

To count cells with values less than a certain number, use the < operator.

Formula: =COUNTIF(A2:A10, "<50")

This will count all cells in the range A2:A10 that have values less than 50.

3. Count Values Equal to a Specific Number:

If you want to count cells that exactly match a given number, use the = operator.

Formula: =COUNTIF(A2:A10, "=100")

This will count all cells in the range A2:A10 that are exactly 100.

3. Count Cells with Specific Dates:

If you want to count cells that match a specific date, you can use the COUNTIF function with the date in quotes. Dates in Excel are treated as serial numbers, so you can directly use them in the formula.

Formula for an Exact Date:

=COUNTIF(A2:A10, "2024-01-01")

This will count how many times the exact date "2024-01-01" appears in the range A2:A10.

Formula for Dates Greater Than or Less Than a Specific Date:

• Greater than a specific date: =COUNTIF(A2:A10, ">2024-01-01")
  This counts all cells in the range that have dates after "2024-01-01".
• Less than a specific date: =COUNTIF(A2:A10, "<2024-01-01")
  This counts all cells in the range that have dates before "2024-01-01".

4. Count Blank and Non-Blank Cells:

You can use the COUNTIF function to count blank cells or cells that are not blank in a specified range:

1. Count Blank Cells:

To count blank or empty cells, use the "" (empty string) as the criteria.

Formula for Counting Blank Cells:

=COUNTIF(A2:A10, "")

This counts how many cells are blank in the range A2:A10.

2. Count Non-Blank Cells:

To count cells that are not empty (i.e., cells that contain any data), use the <>"" operator, which means "not equal to an empty string."

Formula for Counting Non-Blank Cells:

=COUNTIF(A2:A10, "<>")

This counts all cells that are not empty in the range A2:A10.

To Read More about COUNTIF Function for Exact and Partial Match Click Here

Conclusion

Mastering the COUNTIF function in Excel opens the door to efficient data analysis. With its ability to handle everything from simple counts to advanced criteria like partial matches and date-specific searches, it’s an essential tool for professionals and beginners alike.

Start by applying the examples shared in this guide and explore how to use COUNTIF in Excel to handle your data with ease. With practice, you’ll find that COUNTIF not only saves time but also adds precision to your data management tasks.