The COUNTIF function in Excel is used to count the number of cells that meet a specific condition. It is extremely useful for analyzing data such as counting sales above a value, identifying repeated entries or tracking text-based categories.
COUNTIF follows the syntax:
=COUNTIF(range, criteria)
It checks a given range and returns how many cells satisfy the specified condition.
Using the COUNTIF Function in Excel
Step 1: Prepare our 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 we 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 we want to evaluate. For instance, if we'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 our Result.
In the below example, we will understand how to use COUNTIF Function in Excel.
Excel COUNTIF Function Example
We have a list of employees in the table and we want to count how many of them belong to the Marketing department. We’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.
Step 2: Select the Cell to Input the Formula
Click on the cell where we want the result to appear. Here we have selected I5.
=COUNTIF()
.webp)
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)
.webp)
Step 4: Enter the Criteria and Press Enter
Specify the criteria as "Marketing" (the text we want to count). We 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)
.webp)
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).
.webp)
Excel COUNTIF with Multiple Criteria
COUNTIF Function is not exactly designed to count cells with multiple criteria. The COUNTIF function is used to count cells that match two or more criteria like AND logic. We 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)
.webp)
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 we to count cells that match partial text. There are two main wildcard characters:
a. 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").
b. 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 us to count cells that meet specific numerical conditions, such as being greater than, less than or equal to a certain number.
a. Count Values Greater than:
To count cells with values greater than a certain number, we 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.
b. 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.
c. Count Values Equal to a Specific Number:
If we 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 we want to count cells that match a specific date, we can use the COUNTIF function with the date in quotes. Dates in Excel are treated as serial numbers, so we 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:
We 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.