The COUNTIF formula in Excel is a useful formula for quickly filtering data based on specific criteria.
For example, you might have a worksheet that includes sales data, and you want to filter the data by product category. The COUNTIF function allows you to do this in just one line of code instead of spending time creating manual filters.
In this post, you will learn everything you need to know about the COUNTIF formula in Excel so that you can make better use of it in your own spreadsheets.
What is the COUNTIF Formula in Excel?
The COUNTIF formula in Excel is a function you can use to count the number of cells that meet your specified criteria.
COUNTIF Formula Syntax:
The specific syntax of the COUNTIF formula is: =COUNTIF (range, criteria)
Where,
- The range is the cell range that you want to count values in
- Criteria is the criteria you want to use for counting cells &
- If is the logical operator that determines if the cells meet the criteria
Getting Started with the COUNTIF Function:
- If you want to use the COUNTIF function, you’ll need to be in the range selection mode. This means that you should have your cursor in the cell range you want to work with and not an individual cell.
- If you are not in the range selection mode, you can get there by clicking on the cell range (i.e., pressing “Ctrl” and clicking on the cells you want to work with).
- After you’re in the right mode,
- You can enter the code =COUNTIF(Range, Criteria) into the cell where you want to see the results appear.
- When you press Enter, Excel will count the number of cells in the selected range that meet the specified criteria and display the results in the cell you entered the formula in.
How to use the COUNTIF function?
To understand how to use the COUNTIF function, let’s go through a quick example.
Imagine you have a worksheet that contains sales data by product category.
If you want to get the number of orders by product category, you could manually create a filter using a drop-down menu. But this becomes tedious and takes a lot of time when you have huge data. The simple way to do this could be using the COUNTIF function.
Let’s say you want to filter the number of orders by Product Category – “Chocolate Chip”
To do this, We will have select the range as “E1:E11” and the Criteria as “Chocolate Chip”
I.e =COUNTIF(E1:E11,”Chocolate Chip”)
COUNTIF Formula Examples:
COUNTIF Formula for an Exact Match – Text Example:
While using the COUNTIF formula for exact text match, we will have to add the text to be searched within quotes.
=COUNTIF(E1:E11,”Chocolate Chip”)
Note: The COUNTIF function is not case-sensitive. It treats both Chocolate Chip and chocolate chip as the same.
COUNTIF Formula for a Number Example:
While using the COUNTIF formula for number count, we will have to add the number to be searched in the criteria.
=COUNTIF(F1:F11,33)
COUNTIF formula can be combined along with logical operators such as >,< & etc. Below are a few examples,
- To find the number of products with Unit Price > 2, we could use =COUNTIF(G1:G11,”>2″)
- To find the number of products with a price not equal to 1.77. We could use =COUNTIF(G1:G11,”<>1.77″)
- Please Note: COUNTIF only supports logical operators (>,<,<>,=)
COUNTIF Formula Using Wildcards Example:
COUNTIF function supports three wildcards that can be used in the criteria argument:
- Question Mark (?): This wildcard matches any one character
- Asterisk(*): This wildcard matches zero or more characters of any kind &
- Tilde (~): This wildcard is an escape character
Examples,
- To match any product that contains “chip” we could use =COUNTIF(E1:E11,”*chip*”)
- To match any product that ends with “ot” we could use =COUNTIF(E1:E11,”*ot”)
- To match any product that starts with “c” we could use =COUNTIF(E1:E11,”c*”)
- To match any New York or New-York we could use =COUNTIF(C1:C11,”new?york”)
Excel COUNTIF Formula Frequently Asked Questions
Can you add two COUNTIF in Excel?
Yes, we can add more than 1 COUNT in excel. If there are more than two criteria that you want to count in one column, we could use =COUNTIF(range1, criteria1) + COUNTIF(range2, criteria2) + COUNTIF(range3, criteria3)
What is the difference between COUNTIF and COUNTIFS?
The major difference between COUNTIF and COUNTIFS is that COUNTIF is designed for counting cells with a single criterion in one range, whereas COUNTIFS is used for multiple criteria in the same or multiple ranges.