How to Use Countif in Excel [Step-by Step]

Excel's potent COUNTIF function allows you to count cells according to a single criterion. 

Numeric Criteria in excel online, 2016 and 2019

To count cells that are more than or equal to a value, count cells that are equal to a value, etc., use Excel's COUNTIF function.

The number of cells that equal 20 is counted using the COUNTIF function below.

1.The COUNTIF function that follows produces the same outcome.

2.The cells that have a value greater than or equal to 10 are counted by the COUNTIF function below.

3.The COUNTIF function that follows produces the same outcome.

4.The number of cells that are less than 7 is counted by the COUNTIF function below.

5.The COUNTIF methods listed below count how many cells have values of 3 or 7.

Text tricks in excel

1.To count cells that contain a certain text, use Excel's COUNTIF function plus a few shortcuts. Text should always be encased in double quotation marks.The number of cells that contain exactly star is counted using the COUNTIF function below.

2.The COUNTIF function listed below determines how many cells have exactly star + 1 character. A question mark (?) corresponds to one single character.

3.Using the COUNTIF function, we can determine how many cells have exactly star + a string of zero or more characters. A series of zero or more characters is indicated by an asterisk (*).

4.The number of cells that include star in any fashion is counted using the COUNTIF function below.

5.The number of cells with text is counted using the COUNTIF function below.

Count Booleans in excel

1.To count Boolean values in Excel, use the COUNTIF function (TRUE or FALSE).The number of cells that have the Boolean value TRUE is counted using the COUNTIF function below.

2.The number of cells that contain the Boolean value FALSE is counted using the COUNTIF function below.

Count errors in excel

1.To count certain errors, use Excel's COUNTIF function.The number of cells that have the #NAME? error is counted using the COUNTIF function below.

2.The array formula below counts all faults over a set of cells.

3.By hitting CTRL + SHIFT + ENTER, you can complete an array formula. The curly braces are added by Excel. Lastly, simply hit Enter to complete. Curly braces won't be seen

What is the COUNTIF function?

The COUNTIF function provides users with the option to count cells in a range as per specific criteria. For example, COUNTIF can be used to find the count of a specific word in a range of cells. Another example of COUNTIF is to count the number of cells that contain a specific number larger or smaller than a specified criterion. Following is the syntax of the COUNTIF function:

COUNTIF (range, criteria)

Where range is the selection of the cells, e.g., A1:A55, and criteria is the logic for the count of cells, e.g., “text” or “

What problem can the COUNTIF function solve?

The COUNTIF functions solve several problems related to data analysis and data filtering. It helps the users to extract specified information from a range of data. For example, COUNTIF can be used to extract the count of specific transactions that exceed a certain amount in sales data.

What are the common COUNTIF functions?

The COUNTIF tool can be used in many ways in Excel. Following are some common functions of COUNTIF in Excel

1. COUNTIF to count text with different conditions:

One of the common uses of the COUTIF is to count a specific text in a range of data. Applying an asterisk (*) at the end or on both sides of the text acts as a wildcard. For example, =COUNTIF(range, "*text*")

2. COUNTIF to count numbers with different numerical conditions:

Another use of COUNTIF is the count of numbers based on a numerical condition. Various numerical conditions can be applied in COUNTIF for example, =COUNTIF(range, ">x") and

=COUNTIF(range, "")>

3. COUNTIF with the logical expression:

One more common use of the COUNTIF function is through logical expressions. For example, =COUNTIF(range, "A1>B1")

How to use COUNTIF in Excel?

Example 1: How to use COUNTIF Function in Excel from another sheet

The range of cells for the COUNTIF function in Excel can be easily selected from another sheet tab. Following are the steps to use COUNTIF in Excel from another sheet:

1. Open your Excel sheet and select the cells where you want to use the COUNTIF function

2. Enter the COUNTIF formula by entering equal to “=COUNTIF(range, criteria)”

3. For the selection of range, you can either directly select the cells from another sheet, or you can type the name of the sheet and an exclamation mark and mention the range of cells that need to be selected.

4. After entering the range and criteria, pressing entering brings the results.

Example 2: How to use COUNTIF in Excel for a range

The syntax of the COUNTIF consists of a range and criteria that allow the users to select a range of cells and count the cells as per a specific criterion. Following are the steps to use COUNTIF in Excel for a range:

1. Select the cell where you want to use the COUNTIF formula

2. Enter the COUNTIF formula by entering the ‘=’ sign

3. The syntax of the COUNTIF will appear as =COUNTIF(range, criteria)

4. Select the cells in place of range on which you want to apply the formula

5. Fill in the specific criteria that you want to use

6. Press Enter to get the results.

Example 3: How to use COUNTIF in Excel with multiple ranges

The COUNTIF function can be used in Excel with multiple ranges only by adding an S with the formula, which makes it COUNTIFS (range1, criteria1, range 2, criteria 2, ….). If Following are the steps to use the COUNTIFS function in Excel:

1. Open the Excel sheet and select the cell where you want to use COUNTIFS

2. Enter the formula in the cell as =COUNTIFS(range 1, criteria 1 …).

3. Replaces the ranges with the selection of the cells you want to analyze.

4. Replace the criteria with the specific requirement of the analysis.

5. Press Enter to get the results.

Example 4: How to use COUNTIF in Excel for dates

The COUNTIF function can be used to count the number of cells based on the criteria of dates. Here are the steps that you can follow to use COUNTIF in Excel for dates:

1. Select the cell in the Excel sheet where you want to apply the formula.

2. Enter the COUNTIF formula in the cell.

3. Select the range of the cells that need to be evaluated on the criteria of dates.

4. Enter the criteria for analysis based on the dates, which will be in the form of =COUNTIF(A1:A10, “01/01/2022”).

5. Press Enter to get the results.

Example 5: How to use COUNTIF in Excel greater than

Another common use of COUNTIF in Excel is counting the cells greater than a specific value. The formula can be used in the following steps:

1. Select the cell in the Excel sheet where you want to use COUNTIF.

2. Enter the COUNTIF formula in the cell.

3. Select the range on which you want to apply the formula.

4. In the criteria, use the > sign with the number, e.g., =COUNTIF(A1:A10, ">10").

5. Press Enter to get the results.

Example 6: How to use COUNTIF in Excel to count text

To use the COUNTIF function in Excel to count text, you can specify the text criteria within the formula. Below are the steps to follow:

1. Open the Excel sheet and select the cell to use COUNTIF.

2. Enter the COUNTIF formula in the cell.

3. Select the range on which you want to analyze the text.

4. In the criteria, you can give multiple conditions to identify text, e.g., =COUNTIF(A1:A10, "Apple").

5. Press Enter to get the results.

Why is my COUNTIF function reporting an error?

• Why is my COUNTIF formula not working in Excel?

If your COUNTIF is not working in Excel, there could be several reasons for it. The first and foremost thing for the COUNTIF to work is its syntax, i.e., correct entry of range and criteria. If there is any error comma or parenthesis, the COUNTIF will not work. Another reason for COUNTIF not to work is the error values in the selected range of cells. Moreover, the COUNTIF will also not work if the data type is improper.

• Why is COUNTIF returning 0 in Excel?

The COUNTIF formula returns 0 in Excel when the range and criteria are not entered correctly and the data types are mismatched.

• Why am I getting a value error on my COUNTIF?

A value error on your COUNTIF formula in Excel indicates an issue with the formula itself or the data being used. The error usually occurs due to non-numeric criteria for a numeric range and the use of invalid characters or symbols in the formula or the data.

• How do I remove the #value error in Excel but keep the formula?

The #value error occurs in Excel when you mix the different data types, e.g., text and numeric values in the data. The  #value error can be removed by using the functions instead of the symbols; e.g., using the sum function instead of the + sign keeps the formula without the #value error.

• Why do I keep getting a value error in Excel?

The #value error occurs in Excel when you mix the different data types, e.g., text and numeric values in the data.

WPS is a comprehensive office tool that allows users to create, manage and edit documents, spreadsheets, and presentations. WPS Office is compatible with a wide range of file formats. Most of the essential features of the WPS office are free for its users. However, there is a premium version of WPS Office as well that offers unlimited access to all the templates and advanced features.

WPS Office offers numerous benefits to its users. The interface of WPS office applications is user-friendly and easy, so users can easily adapt to it. The collaboration features of the WPS office allow many users to work on the same files simultaneously for editing purposes. Additionally, it offers a wide range of templates in Word, Excel, and PowerPoint that will save you a lot of time and effort.

Downloading and installing WPS Office is very easy. You need to visit the WPS office official website, www.wps.com, and download the software to your computer. Follow the installation instructions by running the downloaded file. Once the installation is complete, you can start using the desired document, spreadsheet, and presentation.

FAQs

How do I use Countif from different sheets in Excel?

COUNTIF can be used from different sheets by simply selecting the cells from another sheet when giving range or by typing the name of the sheet followed by an exclamation mark in range.

How do I use Countif and match in Excel?

The COUNTIF function in Excel can be combined with the MATCH function by selecting the criteria for COUNTIF through the MATCH function.

What is the difference between Countif and Countifs?

The difference between COUNTIF and COUNTIFS is the capability of the formula to calculate cells. The COUNTIF function applies only to a single range and criterion. In contrast, the COUNTIFS function allows the users to combine multiple ranges and multiple criteria in a single formula.

Conclusion

In summary, COUNTIF is a useful tool in Excel that enables users to perform data analysis, data filtering, and identifying patterns. The syntax of COUNTIF is very simple and consists of the selection of cells called range and the selection of the conditions called criteria. Some common uses of COUNTIF include counting cells based on numerical, text, and date criteria. Moreover, the COUNTIFS functions allow the users to analyze data based on multiple ranges and criteria.

WPS Office is a comprehensive office suite with various applications and programs like Word, spreadsheets, PowerPoint, and pdf. You can easily download WPS by following the download button on this page. WPS Office provides free access to all these applications' features, making it an attractive choice for its users. Additionally, WPS is available on multiple platforms, such as Windows, macOS, Linux, iOS, and Android.