Open In App

COUNTIF Function in Excel – Step by Step Tutorial

Last Updated: 14 Jan, 2025

J

Jimish Ravat
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Save
Share
Report
News Follow

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.

COUNTIF Function in Excel

Excel COUNTIF Function

Table of Content

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.

COUNTIF Function in Excel

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(

COUNTIF Function in Excel

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,

COUNTIF Function in Excel

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)

COUNTIF Function in Excel

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

COUNTIF Function in Excel

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)

COUNTIF Function in Excel

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

Excel COUNTIF Function – FAQs

How do you use countif in Excel?

To use COUNTIF Function follow the steps below:

  • Open Your Excel Sheet
  • Select the Cell for the Result
  • Enter the COUNTIF Formula “=COUNTIF(D1:D10, “A*”)”
  • Press Enter

How to use countif for multiple criteria?

Use the following steps to use countif for multiple criteria:

  • Open Your Excel Sheet
  • Select the Result Cell
  • Enter the COUNTIFS Formula
  • Specify Criteria Ranges and Criteria
  • Press Enter

What is Excel countif greater than?

The COUNTIF function in Excel is used to count the number of cells that meet a specific criterion. When you want to count cells with values greater than a certain number, you use the COUNTIF function with a “greater than” condition.

=COUNTIF(range, “>value”)

How to count if a cell contains part of text?

To count if a cell contains part of text use the following steps:

  • Open Your Excel Sheet
  • Select the Cell for the Result
  • Enter the COUNTIF Formula “=COUNTIF(A1:A10, “*apple*”)”
  • Press Enter

What is the shortcuts of Excel COUNTIF Function?

You can use some keys on your keyboard for the shortcuts:

  • Open Your Excel Sheet
  • Select the Result Cell
  • Start Typing the Formula
  • Use the Tab Key to autofill
  • Enter Range and Criteria
  • Press Enter

Get IBM Certification and a 90% fee refund on completing 90% course in 90 days! Take the Three 90 Challenge today.

Master Data Analysis using Excel, SQL, Python & PowerBI with this complete program and also get a 90% refund. What more motivation do you need? Start the challenge right away!


Article Tags :

J

Jimish Ravat
News
Improve
Discuss
Do you want to advertise with us?Click here to know more

M

Mansi Sharma

Excel COUNTIF Function for Exact and Partial Match (With Examples)

article_img
The COUNTIF function in Excel is a powerful tool used to count cells that meet specific criteria, whether it's an exact match or a partial match. This function is incredibly useful for managing large datasets, analyzing trends, or summarizing data quickly. For example, you can use COUNTIF to count how many times a specific word appears in a list or identify cells containing partial matches like text strings. With its ability to handle both numeric and text-based data, the COUNTIF function simplifies tasks like tracking inventory, analyzing survey responses, or checking for duplicates. In this guide, we’ll break down how to use the COUNTIF function for both exact and partial matches with clear examples and step-by-step instructions. Table of Content Count Exact Matches with Text in ExcelCount Exact Matches with Numbers in ExcelCounting Cells with Specific Text Using WildcardsUsing COUNTIFS for Multiple Criteria Understanding COUNTIF FunctionThe COUNTIF function counts the number of cells within a range that satisfy a given condition or "criteria." This makes it ideal for summarizing data or performing quick checks across large datasets. Here’s the syntax of the function: =COUNTIF(ra
Read More

P

Prakhar Awasthi

How to use the COUNTIF Function in Google Sheets

article_img
The COUNTIF function in Google Sheets is an essential tool for users who need to perform conditional counting within their spreadsheets. Whether you are organizing data, analyzing trends, or tracking performance, the COUNTIF formula allows you to count cells based on criteria that you set. This makes it easier to quickly assess large datasets without the need for manual counting. By applying specific conditions—such as numerical values, text, or dates—COUNTIF helps you extract meaningful insights and speeds up your data analysis. With just a few simple steps, you can count data points that meet your exact needs, making your spreadsheet tasks much more efficient. Google Sheets COUNTIF FunctionThe COUNTIF function in Google Sheets counts the number of cells within a given range that meet a specific condition. It’s useful for conditional counting, such as counting how many times a particular value or criteria appears, like counting sales greater than a certain number or how many times a word appears in a list. How It WorksRange: Select the cells to check.Criterion: Define the condition (e.g., a number, text, or logical expression).Count Matching Cells: The function counts how many cel
Read More

M

Mansi Sharma

How to Use Greater Than, Less Than, or Equal to in COUNTIF Function (With Examples)

article_img
The COUNTIF function in Excel is a powerful tool that enables you to count the number of cells in a specific range that meet a condition you set. One of the most common tasks you'll encounter is counting cells based on certain criteria, such as whether the value is greater than, less than, or equal to a particular number. Whether you're analyzing sales performance, tracking inventory levels, or evaluating test scores, this function can save you time and help you draw meaningful insights from your data. In this guide, we'll explain how to use the COUNTIF function with greater than, less than, and equal to conditions, providing practical examples and formulas to help you master this feature. Table of Content Using COUNTIF to Count Cells Based on ConditionsExample 1: Counting Cells with Values Greater Than a Specific NumberExample 2: Counting Cells with Values Less Than a Specific NumberExample 3: Counting Cells with Exact Values (Equal to) Syntax of the COUNTIF FunctionThe basic syntax for the COUNTIF function is: =COUNTIF(range, criteria) range: The range of cells you want to evaluate.criteria: The condition you want the cells to meet, such as a specific number, text, or logical ope
Read More

R

Rishabh Mishra

How to Use the UNIQUE Function in Excel: Step by Step Guide

article_img
Do you struggle with manually removing duplicates in Excel? The UNIQUE function is here to make your life easier! In this article, you’ll learn how to quickly extract unique values from a list or range using the Excel UNIQUE function. Let’s get into the step-by-step process, formula examples, and everything you need to know. Table of Content What is the Unique Function in ExcelKey Features of the UNIQUE FunctionHow to Use the UNIQUE Function to Remove DuplicatesHow Does the UNIQUE Function Work?Advanced Usage of the UNIQUE FunctionWhat is the Unique Function in ExcelThe UNIQUE function in Excel is a powerful tool that allows you to automatically filter out duplicate values from your data. Whether you’re dealing with a long list of names, numbers, or other data, the UNIQUE function extracts only the distinct entries, saving you time and effort. This function is part of Excel's dynamic array features and is particularly useful when working with large datasets, making data analysis and reporting a breeze. Key Features of the UNIQUE FunctionThe UNIQUE function in Excel is a part of the dynamic array features and has a few vital information to maintain in mind. Here are some points to
Read More

A

Ayush Dey

How to Sort Data in Excel: Easy Step by Step Process

article_img
Sorting data in Excel is a fundamental skill that can greatly enhance your data analysis capabilities. Whether you're dealing with a simple list or a complex dataset, knowing how to sort data in Excel can help you organize and make sense of your information quickly and efficiently. In this article, you will learn the easy, step-by-step process of sorting data in Excel. From basic alphabetical or numerical sorting to more advanced multi-level sorts, you'll learn how to manage your data like a pro. Know how to organize your data in Excel by using some sorting techniques. Microsoft Excel offers two widely used features: sorting and filtering. They are frequently used in data analysis to set up, organize, and subset your data according to particular criteria. You will discover how to sort data in Excel in this article. You will also get knowledge of data filtering. Table of Content What is Sorting in ExcelHow to Sort a Single Column in ExcelHow to Sort Multiple Columns in ExcelHow to Apply a Custom Sort in Excel Types of Filter in ExcelHow to Apply Filter in ExcelHow to Apply an Advanced Filter in ExcelWhat is Sorting in ExcelSorting is the process of arranging the strings or integers
Read More

A

Ankur Pal

How to Create a Graph in Excel: A Step-by-Step Guide for Beginners

article_img
Anyone who wants to quickly make observations and represent them graphically should know how to create graphs with Excel. Whether it is the preparation of business analysis papers, academic research documents or financial reports among other things, learning how to make graphs in Excel can significantly improve the way you present information derived from different sources. By exploring Excel’s vast array of options, you have what it takes to convert raw information into coherent visuals that immediately reveal any trends, consistencies, and anomalies, also we will encounter to create charts in Excel from data. In this article, we will learn to make graphs in Excel or create a graph in Excel along with the several categories of graphs such as creating pie graphs in Excel, bar graphs in Excel and so on. Table of Content What is a Chart or Graph in ExcelHow to Create Charts in ExcelType of Charts in MS ExcelHow to Create Surface Graph in MS ExcelHow to Create Histogram in ExcelHow to Format the ChartHow to Change Chart TypeWhat Are Charts and Graphs in ExcelCharts and graphs in Excel are powerful tools for data visualization. They help you present and analyze data graphically, making
Read More

R

Rahul kushwaha

How to use Copilot in Excel with Examples (Step-by-Step Guide)

article_img
As of 2024, Microsoft introduced a new feature called Copilot for Excel, part of the broader Microsoft 365 suite, designed to enhance productivity and streamline data manipulation using AI-powered tools. The introduction of Excel Copilot is transforming how professionals across industries manage and interpret their data. This AI-driven tool enhances productivity, automates repetitive tasks, and simplifies complex data analysis. In this detailed guide, we'll explore how to effectively use Copilot in Excel, complete with practical examples to illustrate its capabilities. Table of Content What is Copilot in Excel?How to Access Copilot in ExcelExcel Copilot Advantages in ExcelHow to Use Copilot in Excel (November 2024)Different Ways to use Copilot in Excel (Including Copilot Prompt)What is Copilot in Excel?Microsoft Excel Copilot is an innovative, AI-powered feature designed to enhance productivity and streamline data processing tasks within Excel. As a part of the Microsoft 365 suite, Excel Copilot transforms how users interact with data by offering advanced artificial intelligence capabilities that simplify complex operations, automate repetitive tasks, and provide actionable insight
Read More

S

SAPTARSHI MONDAL

How to Insert a Checkbox in Excel: Step-by-Step Guide

article_img
How to Add Checkboxes in Excel: Quick Steps Enable the Developer Tab >>Go to Developer TabControl Group >> Click InsertSelect Checkboxes >>Place the CheckboxesLink the Checkbox to a CellAdding interactive elements like checkboxes to your spreadsheets can significantly improve functionality and organization. Whether you're managing task lists, tracking attendance, or creating interactive reports, learning how to insert a checkbox in Excel is a valuable skill. This guide provides a detailed explanation of how to add checkboxes in Excel, covering everything from enabling developer tools to formatting and customizing the checkboxes to suit your needs. Disclaimer: Ensure your data is well-structured to maximize the benefits of using checkboxes in your spreadsheet. Table of Content What is a Checkbox in ExcelHow to Add a Checkbox in Excel : Step-by-Step GuideHow to Get the Developer Tab in the Excel RibbonHow to Insert a Checkbox in Excel How to Customize the Checkbox in ExcelHow to Insert Checkboxes in Excel without Developer Tab How to Link a Checkbox to a Cell How to Insert Multiple Checkboxes in Excel How to Create To-Do List Using Excel CheckboxesUsing Checkboxes w
Read More

A

Ayush Raj

How to Unhide and Show Hidden Columns in Excel: Step by Step Guide

article_img
How to Unhide all Columns in Excel- Quick StepsOpen MS Excel >> Select the Entire WorksheetRight-click on Column Headers >> Select "Unhide"Columns VisibleUnhiding columns in Excel is a simple but essential task when working with hidden data. Whether to make a worksheet look cleaner or accidentally disappeared, knowing how to unhide them ensures all your information is accessible and organized. This guide will walk you through step-by-step methods to unhide columns in Excel, helping you efficiently manage and analyze your data. Table of Content Why Do Columns Get Hidden in ExcelHow to Unhide All Columns In Excel (Windows and Mac)How to Unhide All Columns in Excel Using the Format ToolHow to Unhide All Columns by Setting the Column WidthHow to Unhide Columns in Excel Using Keyboard ShortcutHow to Unhide all Columns Automatically with VBA MacroHow to Show All Hidden Columns in ExcelHow to Check the Number of Hidden Columns in a WorkbookAlternative Method to check the Hidden Column in Excel Why Do Columns Get Hidden in ExcelColumns may be hidden intentionally to: Simplify data presentation.Protect confidential information.Organize large datasets.Columns may also appear hidd
Read More

S

SAPTARSHI MONDAL

How to Protect a Sheet in Excel: Step by Step Guide

article_img
How to Protect a Sheet in Excel- Quick Steps Right Click on the Worksheet Tab Select Protect Sheet >>Enter Password Select the Actions you want to allow the UsersClick OkWhen working with sensitive or shared data in Excel, ensuring the security of specific sheets is essential. Excel sheet protection allows you to safeguard the contents of your worksheet, restricting edits while still enabling other users to view the data. Whether you want to secure a worksheet in Excel or protect an entire workbook, Excel offers multiple methods to maintain data integrity and prevent unauthorized changes. In this guide, we will cover step-by-step instructions for protecting Excel sheets using the context menu and Review tab, securing entire workbooks, and even protecting your Excel files. These techniques will help you keep your data secure while maintaining collaborative efficiency. Disclaimer: Always ensure that you keep your passwords secure and regularly back up your work to avoid data loss or any potential security threats. Table of Content How to Protect a Sheet in ExcelHow to Protect a Excel Workbook How to Protect the Files in Excel How to Protect a Sheet in ExcelProtecting a sheet in
Read More
three90RightbarBannerImg
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox