In Microsoft Excel, the **COUNTIF** function is widely used to count cells with a given condition or criterion. In this article, you’ll get to learn how you can use this **COUNTIF** function efficiently in Excel with proper illustrations.

The above screenshot is an overview of the article, representing an application of the **COUNTIF** function in Excel. You’ll learn more about the dataset as well as the methods to use the **COUNTIF** function properly in the following sections of this article.

**Introduction to the COUNTIF Function**

**Function Objective:**

*Counts the number of cells within a range that meets the given condition.*

**Syntax:**

*=COUNTIF(range, criteria)*

**Arguments Explanation:**

Argument | Required/Optional | Explanation |
---|---|---|

range |
Required |
Range of cells where the criteria will be assigned for counting. |

criteria |
Required |
Condition or criterion for the selected range of cells. |

**Return Parameter:**

*Total counts of the cells in a numerical value.*

**COUNTIF Functions in Excel: ****10 Suitable Applications**

**1. Use of COUNTIF Function with Comparison Operator in Excel**

Before getting down to the uses of the **COUNTIF** function, let’s get introduced to our dataset first. The following picture represents a table containing data from a contest among participants from different states.

In this section, we’ll use a comparison operator inside the **COUNTIF** function to show how many participants have scored more than 70 in the contest.

**📌**** Steps:**

➤ Select the output cell **C24** and type:

`=COUNTIF(F5:F19,C22)`

**Or,**

`=COUNTIF(F5:F19,">70")`

➤ Now press **Enter **and you’ll be shown the number of participants who have scored more than 70 in the contest.

**2. Using COUNTIF Function with Text Criteria in Excel**

In the criteria argument of the **COUNTIF** function, we can use a text value to find out the number of occurrences based on that text value. From our table, we can pull out how many participants are from the state of* Alabama*.

**📌**** Steps:**

➤ In the output cell** C24**, the related formula will be:

`=COUNTIF(E5:E19,C22)`

**Or,**

`=COUNTIF(E5:E19,"Alabama")`

➤ Press** Enter** and the function will return 4, so a total of 4 participants are from* Alabama* state.

**Read More: **Excel COUNTIF to Count Cell That Contains Text from Another Cell

**3. COUNTIF Function to Count Blank or Non-Blank Cells**

Sometimes, our dataset may contain a few blank cells in a column. We can use the **COUNTIF** function to find the number of blank and non-blank cells easily. Assuming, column** B** in the following table has some empty cells we’ll find out the total number of the cells in that column excluding all empty cells.

**📌**** Steps:**

➤ The required formula in the output cell** C24** will be:

`=COUNTIF(B5:B19,"<>")`

➤ After pressing **Enter**, you’ll be displayed the resultant value right away.

If we need to count the blank cells in column** B**, then the required formula in cell** C24** will be:

`=COUNTIF(B5:B19,"")`

Then press **Enter **and you’ll find the total number of blank cells in the output cell.

**4. Including Dates Criteria inside the COUNTIF Function in Excel**

Now our dataset contains a column that includes the birth dates of all the participants. We’ll find out the number of participants who were born after 1995.

**📌**** Steps:**

➤ Select cell** C24** and type:

`=COUNTIF(D5:D19,">12/31/1995")`

➤ Press** Enter **and the resultant value will be 10.

You have to keep in mind the date format in the criteria argument has to be **MM/DD/YYYY**.

**Read More: **How to Use COUNTIF to Count Date Less Than Today in Excel

**5. Application of Wildcards inside the COUNTIF Function for Partial Match Criteria**

By using a wildcard character such as Asterisk (*), we can find out the total counts of text data will the partial match. For example, in Column** E**, we can apply this wildcard character to find how many participants are from a state that contains the text *‘lab’* inside its name.

**📌**** Steps:**

➤ In the output cell** C22**, the related formula for the partial match should be:

`=COUNTIF(E5:E19,C22)`

**Or,**

`=COUNTIF(E5:E19,"*lab*")`

➤ Now press **Enter** and you’ll find the total number of the participants from the *Alabama* state as it contains the defined text *‘lab’* in its name.

**Read More: **[Fixed] COUNTIF Function with Wildcard Not Working in Excel

**6. COUNTIF Function for Values Greater Than and Less Than Criteria**

In this section of the article, we’ll figure out the number of participants whose ages are greater than 23 but less than 27. What we’ll do here is find the total number of participants aged greater than 23 years old first. Then we’ll pull out the number of participants aged equal to or more than 27 years old. Finally, we’ll subtract the second output from the first one to determine the total number of participants based on our defined criteria.

**📌**** Steps:**

➤ Select the output cell** C24** and you have to type:

`=COUNTIF(D5:D19,">23")-COUNTIF(D5:D19,">=27")`

➤ After pressing** Enter**, you’ll get the total number of participants whose ages are between 23 and 27 years.

**Read More: **How to Use COUNTIF Function in Excel Greater Than Percentage

**7. Use of COUNTIF with Multiple OR Criteria in Excel**

Now we’ll apply two different criteria in a single column. We’ll find out the number of total participants from *Texas *and* Colorado* states.

**📌**** Steps:**

➤ In the output cell** C24**, the required formula will be:

`=COUNTIF(E5:E19,D21)+COUNTIF(E5:E19,D22)`

**Or,**

`=COUNTIF(E5:E19,"Texas")+COUNTIF(E5:E19,"Colorado")`

➤ Press **Enter** and you’ll be shown the resultant value at once.

**Read More: **Excel COUNTIF Function with Conditional Formatting

**8. Using COUNTIF to Find Duplicates in Two Columns**

There are two lists of random names in column** B **and column** C** in the picture below. We’ll use the **COUNTIF** function here to determine the total number of duplicates or matches of the names between these two columns.

**📌**** Steps:**

➤ Select the output cell** B22** and type:

`=SUMPRODUCT(--(COUNTIF(B5:B19,C5:C19)>=1))`

➤ Press **Enter **and you’ll see a total of 4 names that are present in both columns.

**🔎**** How Does This Formula Work?**

➤ Here **COUNTIF** function extracts the total number of matches for each name in an array and returns:

**{1;0;0;1;0;0;0;1;0;0;0;0;1;0;0}**

➤ With the comparison operator, the resultant values convert the numbers equal to or more than 1 into a logical value-** TRUE**, and for 0, it returns **FALSE**. So, the overall return values look like then:

**{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}**

➤ The Double-Unary(–) used before the **COUNTIF** function converts all of these booleans into numerical values-** 1(TRUE)** and **0(FALSE)**. So, the function will return:

**{1;0;0;1;0;0;0;1;0;0;0;0;1;0;0}**

➤ Finally, **the SUMPRODUCT function** sums all these numerical values and returns 4.

**Read More: **How to Compare Two Columns Using COUNTIF Function

**9. Use of COUNTIF to Extract Unique Values from Two Columns in Excel**

To find the total counts of the unique values from two columns, we have to use a similar formula mentioned in the previous section. This time we have to apply the logical formula **COUNTIF() = 0** to see in an array if the names in *List 1* or Column** B** are unique or not.

**📌**** Steps:**

➤ The related formula in the output cell** B22** to find the number of unique values from two different columns will be:

`=SUMPRODUCT(--(COUNTIF(B5:B19,C5:C19)=0))`

➤ After pressing **Enter**, you’ll get the desired output right away.

**10. Inserting Named Range in the COUNTIF Function to Count Cell with Criteria**

We can use the named range as the criteria or the criteria range inside the **COUNTIF** function. To name a range of cells we have to select the specific cells first. In the Name Box situated at the top-left corner in the picture below, you have to edit the name of the selected range of cells or an array. For example, here I have selected the range of cells under the *Age* header in column** D **and defined them with the name- *Age*.

By using this named range- *Age*, we’ll find out the total number of participants whose ages are between 23 and 27 years.

**📌**** Steps:**

➤ Select the output cell** C24** and type:

`=COUNTIF(Age,">23") - COUNTIF(Age,">=27")`

➤ Press **Enter **and the formula will return 5.

**Read More: **Excel COUNTIF to Count Cells Greater Than 1

**💡**** Things to Keep in Mind**

🔺 You cannot input more than one criterion in the **COUNTIF** function. You have to use the **COUNTIFS** **function** for the inputs of multiple criteria.

🔺 **COUNTIF **function is not case-sensitive. If you need to count case-sensitive cells, then you have to use **the EXACT function**.

🔺 If you use cell reference with comparison operator then you have to use Ampersand(&) to connect the comparison operator and cell reference, such as** “>”&A1** in the criteria argument.

🔺 If you use 255 or more characters in the **COUNTIF** function, the function will return an incorrect result.

**Download Practice Workbook**

You can download the Excel workbook that we’ve used to prepare this article.

**Concluding Words**

I hope all of the methods mentioned above to use the** COUNTIF **function will now help you to apply them in your Excel spreadsheets with more productivity. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

## Excel COUNTIF Function: Knowledge Hub

**COUNTIF Between Two Cell Values in Excel****COUNTIF Function to Count Cells That Are Not Equal to Zero****Use COUNTIF for Non Contiguous Range in Excel****Use COUNTIF Function to Calculate Percentage in Excel****Use COUNTIF Function with Array Criteria in Excel****Use Excel COUNTIF Between Time Range****Calculate Frequency Using COUNTIF Function in Excel****Use SUMIF, COUNTIF and AVERAGEIF Functions in Excel****Use the Combination of COUNTIF and SUMIF in Excel**

**Difference Between SUMIF and COUNTIF Functions in Excel****Use IF and COUNTIF Functions Together in Excel****Use Nested COUNTIF Function in Excel****Use COUNTIF and COUNTA Functions Together in Excel****Use COUNTIF Function to Count Text from List in Excel****Count Text at Start with COUNTIF & LEFT Functions in Excel****Use COUNTIF Function In Excel to Count Bold Cells****[Solved!]: Excel COUNTIF Returning 0 Instead of Actual Value****[Fixed!] Excel COUNTIF Function Not Working for String “True”**

**<< Go Back to Excel Functions | Learn Excel**