Table of Contents
The COUNTIF function in Excel allows you to count the number of cells that meet one criteria, while the COUNTIFS function allows you to count the number of cells that meet multiple criteria. For example, COUNTIF can be used to count the number of cells with a value greater than 5, while COUNTIFS can be used to count the number of cells with a value greater than 5 and less than 10.
In Excel, the COUNTIF and COUNTIFS functions both count the number of cells in a range that meet a certain condition, but they use slightly different behaviors:
- The COUNTIF function counts the number of cells in a range that meet one condition.
- The COUNTIFS function counts the number of cells in a range that meet several conditions.
The following examples show how to use each function in practice.
Example 1: Using COUNTIF
We can use the following COUNTIF formula to count the number of rows where the value in the range A2:A16 is equal to “Mavs”:
=COUNTIF(A2:A16, "Mavs")
The following screenshot shows how to use this formula in practice:
We can see that a total of 5 cells in the range A2:A16 meet this criteria.
Example 2: Using COUNTIFS
We can use the following COUNTIFS formula to count the number of rows where the value in the range A2:A16 is equal to “Mavs” and the value in the range B2:B16 is equal to “Guard”:
=COUNTIFS(A2:A16, "Mavs", B2:B16, "Guard")
The following screenshot shows how to use this formula in practice:
We can see that a total of 5 rows in the dataset contain “Mavs” in column A and “Guard” in column B.
Note that we can use as many conditions as we’d like within the COUNTIFS function.
For example, we can use the following formula to find the rows that meet three different conditions:
=COUNTIFS(A2:A16, "Mavs", B2:B16, "Guard", C2:C16, ">20")
The following screenshot shows how to use this formula in practice:
We can see that a total of 1 row in the dataset contains “Mavs” in column A, “Guard” in column B, and a value greater than 20 in column C.