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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Use COUNTIF with OR in Excel
How to Use COUNTIF From Another Sheet in Excel
How to Use COUNTIFS with a Date Range in Excel