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

Want to count how many cells contain specific data in Excel? The COUNTIF function in Excel is your go-to formula to count the number of times a word, value, or phrase appears in your Excel sheet. In this guide, we will learn all about the COUNTIF Excel function and cover its simple to advanced examples.

Plus, if you are learning Excel or unsure about how to write formulas, UPDF AI can act as your visual learning assistant. It can explain formulas and help you master how to apply them correctly. Download UPDF and keep reading to learn how to benefit from it.

Part 1. What is the COUNTIF Function?

The COUNTIF function in Excel counts the number of cells that meet a specified condition within a range. Its syntax is:

=COUNTIF(range, criteria)

Here, range represents the group of cells you want to evaluate, and criteria defines what you are looking for. For example, if you want to count how many cells contain values greater than 100 in 10 rows of column E, you would use the formula "=COUNTIF(E6:E15, ">100")".

The COUNTIF Excel function has various use cases. Some of the common ones include:

• Counting sales above or below a target threshold.
• Tracking the number of completed or pending tasks.
• Counting students who scored higher than a specific grade.
• Identifying how many cells contain certain text, such as "Approved".
• Counting dates within a specific range, such as orders in a given month.
• Filtering and summarizing survey responses that match given criteria.

COUNTIFS vs. COUNTIF

The COUNTIF function handles one condition at a time. But what if you want to find specific cells that meet multiple conditions simultaneously? That's where the COUNTIFS function becomes useful.

The COUNTIFS function extends the capability of COUNTIF by supporting multiple conditions at once. Its syntax is:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)

For example, if you want to count how many sales are greater than 500 and occurred in May, you could use:

=COUNTIFS(G7:G18, ">500", F7:F18, "May")

This makes COUNTIFS ideal for complex data analysis where multiple conditions must be met.

Part 2. How to Use COUNTIF in Excel?

Using the COUNTIF function in Excel is straightforward. Assume that you have an Excel sheet of the product names and their total quantities sold in May. You want to use the COUNTIF function to count how many products had sales of more than 20 units.

Follow these simple steps:

Step 1. Click on the cell where you want to display the result.

Step 2. Type the formula:

=COUNTIF(E6:E20, ">20")

Here, "E6:E20" represents the range of cells containing the quantities sold, and ">20" is the condition.

Step 3. Press "Enter". Excel will instantly show how many products met your sales target.

This way, you can use the COUNTIF formula in Excel and easily analyze cells that meet specific conditions.

Part 3. Advanced Examples with COUNTIF

Sometimes, we have cells that contain a mixture of numerical values and text. In such cases, we cannot use the COUNTIF function alone. We have to use Excel's Text-to-Columns feature to split the text and numeric values into separate columns, then use COUNTIF on the split data.

Let's look at the advanced example of using the COUNTIF function in Excel with the Text-to-Columns feature. Consider that you have an Excel sheet with a single column containing customer names, addresses, and emails. You want to split them into separate cells and then count the number of customers on a specific street.

Step 1. Highlight the data you want to split and click Data > Text to Columns.

Step 2. Select "Delimited" and click "Next".

Step 3. Select the delimiters that separate different values. In our case, it's the "Comma". Afterward, hit Next > Finish.

Step 4. Now your columns are split, with each representing specific information. Next, we will use the Excel COUNTIF function. So, the syntax is:

=COUNTIF(F5:F14, "*Broadway Avenue*")

This formula searches the address column for cells containing the text "Broadway Avenue" and counts them.

Part 4. How UPDF AI Can Help You Learn COUNTIF and Other Excel Functions

COUNTIF is a useful Excel function to count specific cells, but how to use it effectively can sometimes be tricky. If you are new to formulas or don't like remembering them all, you can have a learning companion with you. That's what UPDF AI assistant has to offer.

UPDF AI is a smart AI assistant powered by GPT-5 and Deepseek R1 advanced AI models. It helps explain formulas and provides step-by-step guidance on how to use them. All it requires is explaining your queries in a simple text prompt and getting the assistance right away.

For example, if you want to know how to count cells containing "Broadway Avenue" in a column, you can ask UPDF AI:

“How can I use the COUNTIF function to count cells containing "Broadway Avenue" in Excel?”.

UPDF AI will then explain the COUNTIF formula and provide a step-by-step guide on how to use it.

Steps to Use UPDF AI Assistant on Desktop

Step 1. Install and launch the UPDF desktop app on your computer. Drag and drop the Excel file to the main dashboard of UPDF.

Step 2. Once the Excel file opens, click the "UPDF AI" icon from the top right corner and select the "Chat" tab.

Step 3. In the chat box, you can copy-paste values or use the screenshot capture option to capture the cells. Afterward, type the prompt explaining what assistance you want. For example, you can ask “How can I use the COUNTIF function to count cells containing "Broadway Avenue" in Excel?” UPDF will instantly give you the exact count.

Steps to Use UPDF AI Assistant Online

Step 1. Go to https://ai.updf.com/chat-bot/ and sign in.

Step 2. Copy-paste the values or share the screenshot of the table in the chat box.

Step 3. Type the prompt: “How can I use the COUNTIF function to count cells containing "Broadway Avenue" in Excel?” Watch how quickly UPDF AI gives you the answer.

This way, UPDF AI acts as a teaching aid and helps you become proficient with Excel formulas.

Download the UPDF desktop app or head to UPDF AI online and become self-sufficient in Excel.

The Other Features of UPDF

UPDF is more than just an AI assistant. Some of the other features you can access with UPDF include:

• Convert Excel Sheets to PDF: You can convert a single Excel sheet or batch convert multiple files at once. The format is preserved during conversion.
• Annotate Excel Reports: Add comments, highlights, shapes, stamps, and other annotations for collaborative review.
• OCR To Extract Numbers: Extract data from scanned or image-based reports in 38+ languages with batch OCR support and multilingual detection.

UPDF software offers dedicated apps for Windows, Mac, iOS, and Android. So, get the UPDF license and start using its powerful features today on your preferred device.

Conclusion

The COUNTIF function in Excel is a handy formula for analyzing large amounts of data and counting cells that meet specific conditions. Therefore, start using it to get quick insights without manually scanning through your spreadsheet. On top of that, keep UPDF AI by your side as a learning companion to master Excel formulas without wasting time browsing different resources.