Excel COUNTIF and COUNTIFS Function

What is the COUNTIF function?

COUNTIF is a built-in function in Microsoft Excel and Google Sheets that allows you to count the number of cells within a range that meets certain criteria. It can be useful in various situations, such as counting the number of times a certain value appears in a range of data, counting the number of cells in a range that contain text, counting the number of cells that meet certain numeric conditions, and more. The syntax for the Count of the function is as follows,

COUNTIF (range, criteria)

From the formula, the range is the range of cells you want to count, and the criteria are the condition the cells must meet to be counted. For example, if you have a range of numbers in cells A1 to A10 and you want to count how many of those numbers are greater than 5, then the formula is modified as,

=COUNTIF (A1:A10,">5")

List of Criteria Used in Count of Functions

Based on the data requirement, the criteria are modified. Some of the criteria are explained as follows,

1. Greater than or Lesser than

In the COUNTIF function, you can use the greater than (">") and less than ("<") operators to specify a numeric condition.

Example: Count the data which are greater than 100

Step 1: Enter the required data in the spreadsheet A1:A10

Step 2: Select a new cell where the user wants to display the result, namely B1, and enter the formula =COUNTIF (A1:A10,">100")

Step 3: Press Enter. The number of data above 100 will be displayed in cell B1.

Here in the worksheet, six numbers of data are greater than 100. The COUNTIF function returns the result in cell B1.

Example: Count the data which are lesser than 100

Step 1: Enter the required data in the spreadsheet A1:A10

Step 2: Select a new cell where the user wants to display the result, namely B1, and enter the formula =COUNTIF (A1:A10,"<100")

Step 3: Press Enter. The number of data lesser than 100 will be displayed in cell B1.

Here in the worksheet, four numbers of data are lesser than 100. The COUNTIF function returns the result in cell B1.

2. Equal to and Not Equal to

In Excel, the equal to the operator is represented by the "=" symbol, and the not equal to the operator is represented by the "<>" symbol. These operators are used in formulas and conditional formatting to compare values and make decisions based on the results of those comparisons.

Example: Display the Count of data equal to the word "Google."

Step 1: Enter the respective data in the worksheet, namely A1:A10

Step 2: Select a new cell where you want to display the result, namely B1, and enter the formula as =COUNTIF (A1:A10, Google)

Step 3: Press Enter. The result will be displayed in cell B1.

Here in the worksheet, from cell A1:A10, the word "Google" is repeated twice. Hence the COUTIF function returns the result as 2 in cell B1.

Example: Display the Count of data that is not equal to the word "Google."

Step 1: Enter the respective data in the worksheet, namely A1:A10

Step 2: Select a new cell where you want to display the result, namely B1, and enter the formula as =COUNTIF (A1:A10,"<> Google")

Step 3: Press Enter. The result will be displayed in cell B1.

In the worksheet, different types of search engines are given. Based on the criteria, the COUTIF function returns the result as 8 in cell B1.

3. Blank and Non-Blank Cells

As the name suggests, the blank and non-blank functions are used to find the number of blank and non-blank cells in data.

Example: Count the number of blank cells in the given data

Step 1: Enter the respective data in the worksheet, namely A1:A10

Step 2: Select a new cell where you want to display the result namely B1 and enter the formula as =COUNTIF (A1:A10,"")

Step 3: Press Enter. The result will be displayed in cell B1.

There are three blank cells present in the worksheet. The COUTIF function returns the result as 3 in cell B1.

Example: Count the number of non-blank cells in the given data

Step 1: Enter the respective data in the worksheet, namely A1:A10

Step 2: Select a new cell where you want to display the result, namely B1, and enter the formula as =COUNTIF (A1:A10,"<>")

Step 3: Press Enter. The result will be displayed in cell B1.

There are seven non-blank cells present in the data A1:A10. The Count if function displays the result as 7 in cell B1.

4. Count if for calculating the Date

The Count if the function is used in the Date function using less than the logical operator.

Step 1: Enter the required Date in the worksheet, namely A1:A5

Step 2: Select a new cell where the user wants to display the result, B1.

Step 3: To calculate the Count of data that is lesser than today's Date, enter the formula as =COUNTIF ("<"&TODAY ())

In the worksheet from the cell range A1:A10, seven dates are lesser than today. Hence the COUNTIF function returns the Count of Date as 7 in the selected cell.

COUNTIF function with OR function

To apply multiple criteria with Count if and Countifs functions, AND and OR logic is used. The Count a function is used to count cells with a single condition in one range, whereas COUNTIFS evaluates different criteria in the same or different range.

Example: Calculate the status of each product

Here is an example that describes the status of each product. To apply multiple criteria COUNTIF function is used with OR functions.

Step 1: Enter the data in the worksheet, namely A1:C11

Step 2: The 11 products and their price and status are mentioned here. To count the status of the different products COUNTIF function is used with OR functions.

Step 3: To display the Count of products that are SOLD or Delivered, select a new cell, namely E1, where the user wants to display the result and enter the formula as =COUNTIF ($C$2:$C$11, "Delivered")+ COUNTIF($C$2:$C$11, "SOLD").

Step 4: Press Enter. The Count of items sold or delivered is displayed in cell E1.

In the worksheet, the Count of seven products is in the status of Delivered or Sold. Similarly, one can find the Pending status of the product. Based on the requirement, the formula is modified.

What is COUNTIFS' Function?

The COUNTIFS function is an Excel function that allows you to count the number of cells in a range that meets multiple criteria. You can specify up to 127 different criteria across multiple ranges. The syntax of the function is as follows,

=COUNTIFS (range 1, criteria1,[range2],[criteria2],?)

Where range1, range2etc. are the range of cells you want to evaluate, and criteria 1, 2, etc., are the conditions you want to apply to each range. For example, the formula =COUNTIFS(A1:A10,">=5", B1:B10,"<10") will count the number of cells in the range A1:A10 that contain values greater than or equal to 5 and in the range B1:B10 that contain values less than 10.

COUNTIFS function with multiple criteria

COUNTIFS with OR Condition

As previously explained, the COUNTIFS function is used for multiple criteria. Here COUTIFS function is used with the OR function.

Example: Evaluate the given data using the COUNTIFS function

This example gives the list of various items and their delivery status. To find the items' selective status, the COUNTIFS function with OR condition is used.

Step 1: Enter the data in the worksheet, namely A1:C11.

Step 2: To find the Count of status-given products, select a new cell and enter the formula as =COUNTIFS (A1:A10, E1, B1:B10, E2) +COUNTIFS (A1:A10, F1, B1:B10) +COUNTIFS (A1:A10, G1, B1:B10, E2).

Step 3: Press Enter. The result will be displayed in the selected cell.

Here is the formula, the cell named E1, F1, G1, and E2 are user-defined with product names and statuses.

COUNTIFS with array constant

Here in this method, the OR criteria are packed in an array constant using AND/OR logic.

Example: Evaluate the data using COUNTIFS with an array constant

Step 1: Enter the data in the worksheet, namely A1:B10

Step 2: Select a new cell where the user wants to display the result, namely E3, and enter the formula as =SUM (COUNTIFS (A1:A10, E1:G1, B1:B10, E2)).

Step 3: Press Enter. The result will be displayed in the selected cell.

Here is the formula, the cell name E1, F1, G1, and E2 are user-defined product names and statuses in the individual cells.

Summary

In this tutorial, the COUNTIF and COUNTIFS function is explained with examples. In conclusion, the COUNTIF and COUNTIFS functions are useful for counting cells in a range that meets specific criteria or conditions. COUNTIF is used for a single criterion, while COUNTIFS allows multiple criteria to be evaluated simultaneously.