Tutorials

Search... Practice
Sign In
Jobs

COUNTIF Function in Excel

Last Updated : 13 Jan, 2026

The COUNTIF function in Excel is used to count the number of cells that meet a specific
condition. It is extremely useful for analyzing data such as counting sales above a value,
identifying repeated entries or tracking text-based categories.

COUNTIF follows the syntax:

=COUNTIF(range, criteria)

It checks a given range and returns how many cells satisfy the specified condition.

New Google
AI Certificate
Enroll in the new Google AI
Professional Certificate today.

Coursera

Using the COUNTIF Function in Excel

Step 1: Prepare our 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 we 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 we want to evaluate. For instance, if we'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 our Result.

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

Excel COUNTIF Function Example

We have a list of employees in the table and we want to count how many of them belong to the
Marketing department. We’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 we 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 we want to count). We 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).

a Analysis in Excel Formatting Workbooks Statistical Functions Data Visualization in Excel Pivot Ta Sign In

Excel COUNTIF with Multiple Criteria

COUNTIF Function is not exactly designed to count cells with multiple criteria. The COUNTIF
function is used to count cells that match two or more criteria like AND logic. We 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)

Use "=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 we to count cells that match partial text. There are two
main wildcard characters:

a. 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").

b. 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 us to count cells that meet specific numerical conditions,
such as being greater than, less than or equal to a certain number.
a. Count Values Greater than:
To count cells with values greater than a certain number, we 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.
b. 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.
c. Count Values Equal to a Specific Number:
If we 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 we want to count cells that match a specific date, we can use the COUNTIF function with the
date in quotes. Dates in Excel are treated as serial numbers, so we 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:

We 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.

Suggested Quiz 5 Questions

What will the following formula return if the range A2:A10 contains the numbers: 2, 6, 8, 12, 16, 5, 9, 14, 3?
=COUNTIF(A2:A10, ">10")

A 2

B 3

C 4

D 5

Login to View Explanation 1/5 < Previous Next >

Comment J jimishr… 4

Article Tags: Excel SQL-Query Excel-functions Excel-basics +2 More

Company Explore Tutorials Courses Preparation

About Us POTD Programming ML and Data Corner
Corporate & Communications Address: Legal Practice Languages Science Interview
Privacy Problems DSA DSA and Corner
A-143, 7th Floor, Sovereign Corporate
Tower, Sector- 136, Noida, Uttar Policy Connect Web Placements Aptitude
Pradesh (201305) Careers Blogs Technology Web Puzzles
Contact Us Upskill AI, ML & Development GfG 160
Registered Address:
Corporate Courses Data Science Data Science System Design
K 061, Tower K, Gulshan Vivante Solution DevOps Programming
Apartment, Sector 137, Noida, Gautam
Campus CS Core Languages
Buddh Nagar, Uttar Pradesh, 201305
Training Subjects DevOps &
Program GATE Cloud
School GATE
Subjects
 Software and MongoDB
Tools Certifications

@GeeksforGeeks, Sanchhaya Education Private Limited, All rights reserved