Excel Countif Function: Simple Guide For Beginners

excel excel formulas excel functions Feb 18, 2023

Are you trying to work out how to use the Excel Countif Function?

Excel's Countif Function can be intimidating for those unfamiliar with formulas, but this tool allows you to count, sum, or average cells based on specific criteria.

This article will help break down the Countif function so anyone can use it.

We will cover the following:

What is the Excel Countif Function?
Excel Countif Function syntax.
The different uses of Excel Countif Function.
How to use the Countif Function Formula for multiple criteria.

What is the Excel Countif Function?

The Countif function counts how many cells in a range meet a specific condition. This condition is called the criteria. The Countif function is a way to count Excel cells containing dates, numbers, or text.

Excel counts the number of cells using the Countif function in a range that meets a specific condition.

For Logical operators: (>,<,<>,=)

For Wildcard characters for partial matching: (*,?)

Excel Countif Function Syntax.

The Excel Countif Function Syntax:

=COUNTIF(range, criteria)

The range is the number of cells you want to count. You can put the range in a formula as you do in Excel. For example, A1:A10.
The criteria define what condition the function is looking for. This can be a number, text string, cell reference, or expression. For example, you can use criteria like these: "5", A2, ">=5", and "sample text."

Different Uses of Excel Countif Function Examples.

The COUNTIF formula can be used for both text and numbers.

The formula for cells that contain exact text strings: =COUNTIF(D3:D20," Roger Federer"). Thus, you proceed:

The first parameter is the range.
A comma is a type of punctuation used to detach parts of a sentence.
A word or several words are confined in quotes as the criteria.

You can use a cell reference to get the same results as if you had typed out the word or words. For example:

=COUNTIF(D2:D10,D8)

The COUNTIF formula can also be used for numbers. The example below shows how to count the cells with quantity 6 in Column B:

=COUNTIF(B2:B8,6)

Select a cell reference, and type the Countif Formula for text and numbers.

Press Enter, and the function automatically counts the cells containing six quantities.

You can use COUNTIF formulas with wildcard characters to count cells.

You can use a wildcard character if your Excel data has different versions of the keyword(s) you want to count. This will help you count all cells with specific words, phrases, or letters.

If you have a list of projects assigned to different people and want to know how many projects are set to Shonny Black, you can enter "Black" as the search criteria. This will help you find all the projects assigned to someone with the last name Black, including Shonny.

=COUNTIF(B2:B9,"*Black*")

Select a cell reference, and type the Countif Formula for wildcard characters.

Press enter, and the function will automatically count the cells that contain "Black" in their respective names.

The COUNTIF function is used to count blank cells and non-blank cells.

The COUNTIF function enables you to count the number of blank or non-blank cells in a specified range.

The COUNTIF formula can count all non-blank cells in a specified range.

=COUNTIF(range,"<>") or =COUNTIF(range,"<>"&"")

This formula is correct for all values - like text, dates, and numbers. You can see an example in the image below.

The COUNTIF Blank counts the number of blank cells in a specific range.

It would be best to use a formula with a wildcard character for text values and with the "criteria to count all empty cells.

Formula to count blank cells:

=COUNTIF(range,"<>"&"*") or =COUNTIF(range,"")

The COUNTIF formula for greater than, less than, or equal to

If you want to count cells with values that meet specific criteria, add the operator corresponding to your desired outcome in the images below. This can include greater than, less than, or equal to a number of your choice.

Count Formula for if greater than:

=COUNTIF(range,">criteria")

Count Formula for if less than:

=COUNTIF(range,"<criteria")

Count formula for if equal to:

=COUNTIF(range,"=criteria")

Count formula for if not equal to:

=COUNTIF(range,"<>criteria")

Count formula for if greater than or equal to:

=COUNTIF(range,">=criteria")

Count formula if less than or equal to:

=COUNTIF(range,"<=criteria")

The COUNTIF formula function with dates.

The COUNTIF function can help you figure out how many cells contain dates before, after, or on the same day as a specific date. The formulas we discussed earlier also work for dates, not just numbers. For example:

Countif formula for same dates up to the specified date:

=COUNTIF(range,"criteria")

Countif formula if the date is equal to or later than the other date:

=COUNTIF(range,">=criteria")

Countif formula For any date that is equal to or greater than a date in another cell, subtract x days:

=COUNTIF(range,">="& 1st criteria -"2nd criteria")

How to use the Countif Function Formula for Multiple Criteria.

People think Excel's COUNTIF can count cells with multiple conditions but it cannot. The COUNTIFS function is designed for tasks like this and uses AND logic. If you need a more complex method, combine two or more functions into one formula!

The Countif formula for cells with multiple OR criteria.

Select a cell where you want to put your formula.

Select a range for your formula.

Press enter, and the Countif function counts cells that contain "Orange" and "Candy."

Final Thoughts on the Excel Countif Function.

The COUNTIF function in Excel is a tool that will help you get your work done quickly and accurately. Learning to use it in various situations will save you time and make data analysis easier.

You can visit our home page for more easy-to-follow how-to and step-by-step guides. Check the links in related articles for further details about Excel Templates!

Frequently Asked Questions on Excel Countif Function:

What is the limitation of the Excel Countif Function?

The COUNTIF function allows you to compare numerical, textual, and logical values in cells with its powerful wildcard character. However, it is unsuitable when analyzing two cell ranges or a huge dataset as this might cause delays in obtaining results.

Why is my Excel Countif Function not working correctly?

You should first ensure you are using the correct information when you call the function, which will help us figure out what is wrong more quickly. If everything looks good there, but the function is not working, try to find help online.

Can Excel Countifs Function detects duplicates?

The function can automatically find duplicate entries in your spreadsheet, which is helpful when you have a lot of data or a long list. Additionally, the function will highlight the duplicates so you can spend time on other project parts.