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
DEAR,
CAN WE USE COUNTIFS TO CHECK 2 POSSIBILITES IN THE SAME CELL RANGE
Hi Syed…Yes, you can use the `COUNTIFS` function in Excel to check for two different conditions in the same cell range. However, `COUNTIFS` only counts cells that meet **all** of the specified criteria across different ranges.
If you want to check for **either** of two possibilities in the same range, you will need to use multiple `COUNTIFS` statements or combine them with `COUNTIF`.
Here’s how you can do it:
### Scenario 1: Checking for **either** of two conditions in the same range
Suppose you want to count cells in a range that contain either “A” or “B.” You can do this by adding two `COUNTIF` functions together:
“`excel
=COUNTIF(A1:A10, “A”) + COUNTIF(A1:A10, “B”)
“`
### Scenario 2: Checking for **both** conditions in different ranges
If you want to use `COUNTIFS` to count cells that satisfy both conditions across different ranges, you can use something like:
“`excel
=COUNTIFS(A1:A10, “A”, B1:B10, “B”)
“`
This will count rows where the corresponding cell in `A1:A10` contains “A” **and** the corresponding cell in `B1:B10` contains “B.”
If your case is more complex or if you encounter specific difficulties, feel free to provide more details, and I’ll guide you further!