Download Excel Workbook
Watch our free training video on the COUNTIF Function for Beginners in Excel:
In this easy-to-understand guide, we’ll uncover the magic of the COUNTIF Function for Beginners in Excel. The COUNTIF function is a handy tool that helps you count cells in a range that match a specific criteria. It’s like having a data counter for various tasks, like data analysis, report creation, and dashboard building.
In this article, we’ll cover topics like COUNTIF’s syntax, examples, helpful tips, and related functions. By the time you finish reading, you’ll be a COUNTIF pro, ready to apply it confidently in your Excel projects –
Let’s explore these methods!
Download the Excel Workbook below to follow along and understand how to use The COUNTIF Function for Beginners in Excel –
Download excel workbookCOUNTIFFunctionforBeginners.xlsx
Basic Syntax of COUNTIF
Before we jump into examples, let’s understand the basic syntax of COUNTIF:
=COUNTIF(range, criteria)
range: This is the range of cells you want to evaluate.
criteria: This is the condition or criteria that you want to apply to each cell in the specified range.
Example 1: Counting Cells with Specific Text
Let’s say we have a list of grocery items in column A, and you want to count how many times Cereal appears in the list. You can use COUNTIF like this:
=COUNTIF(A2:A10, “Cereal”)
This formula will count the number of cells in the range A2:A10 that contain the text Cereal. If you have two “Cereal” entries in the list, the result will be 2.
Example 2: Counting Cells with Numeric Values
Let’s say you have a list of sales figures in column B, and you want to count how many salespersons scored higher than 200. You can use COUNTIF as follows:
=COUNTIF(B2:B10, “>200”)
This formula will count the number of cells in the range B2:B10 that have values greater than 200. If six salespersons scored above 200, the result will be 6.
Example 3: Counting Cells with Dates
Suppose you have a list of project deadlines in column A, and you want to count how many of these deadlines are after a specific date, let’s say, after July 9, 2023. Use this COUNTIF formula:
=COUNTIF(A2:A11, “>07/09/2023”)
This formula will count the number of cells in the range A2:A11 where the date is greater than July 9, 2023. If four projects are due after this date, the result will be 4.
Example 4: Counting Cells with Logical Criteria
Let’s say you have a list of products in column A, and you want to count how many products are classified as High Value or Top Priority. In this case, you want to count cells that meet either of these two criteria:
=COUNTIF(A2:A10, “High Value”) + COUNTIF(A2:A10, “Top Priority”)
This formula adds up the counts of cells in the range A2:A10 that contain either High Value or Top Priority.
Wildcard Characters
You can use wildcard characters like asterisk (*) within the criteria. For example, if you want to count all cells containing words that start with the letter “A,” you can use
=COUNTIF(A1:A10, “A*”)
Using Criteria from Another Cell
You can create the COUNTIF formula dynamic by linking it to criteria stored in other cells. This flexibility enables you to adjust the criteria without the need to alter the formula directly. For instance, if your criteria is located in cell C3, you can use the formula
=COUNTIF(A2:A13, C3)
As you can see, you can change the value in the cell to look for a different criteria within the column.
Multiple Criteria with COUNTIFS
When you need to count cells based on multiple conditions, use the COUNTIFS function. It allows you to specify multiple criteria and ranges. For example, =COUNTIFS(A2:A10, “Stapler”, B2:B10, “>80”) counts cells with “Stapler” in column A and a value greater than 80 in column B.
Related Functions to the COUNTIF
Here are some related formulas that can come in handy when you’re working alongside the COUNTIF function:
COUNTIFS
This versatile function is your go-to for counting cells that satisfy multiple conditions. It operates much like COUNTIF but can handle multiple sets of criteria and corresponding ranges simultaneously. Click here to learn more about the COUNTIFS function.
SUMIF
If you want to sum up values within a range that meet a specific criterion, turn to SUMIF. It shares a similar essence with COUNTIF but, instead of counting, it provides you with the sum of the values that meet your condition. Click here to master the SUMIF function.
SUMIFS
Like COUNTIFS, SUMIFS is used to add values based on multiple criteria. It allows you to specify multiple conditions and multiple ranges. Click here to learn how to use the SUMIFS function.
AVERAGEIF
When it’s time to find the average of values in a range that align with a certain criterion, AVERAGEIF is your friend. Just like COUNTIF, it scrutinizes a range based on a single condition but delivers the average value instead of a count. Click here to learn more about the AVERAGE function.
AVERAGEIFS
Similar to COUNTIFS and SUMIFS, AVERAGEIFS calculates the average of values based on multiple criteria. Click here to master the AVERAGEIFS function!
MAXIFS
To unearth the highest value within a range that complies with various conditions, rely on MAXIFS. It resembles COUNTIFS but goes beyond counting to provide you with the maximum value. Click here to learn about the MAXIFS function in Excel.
MINIFS
Similar to MAXIFS, MINIFS investigates a range through multiple criteria, but this time it presents you with the minimum value instead of a count. Click here to learn about the MINIFS function in Excel!
Conclusion:
COUNTIF is one of the most useful functions that simplifies the task of counting cells based on specific criteria. Whether you’re working with text, numbers, dates, or logical conditions, COUNTIF can help you quickly obtain the desired count without the need for complex formulas. Hopefully, you will be able to use the COUNTIF function to simplify your data analysis in your next project!
Click here to access Microsoft’s tutorial on How to Use the COUNTIF Function!
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.
