Table of Contents
The COUNTIF function in Excel allows you to count the number of cells that meet a certain criteria. It can also be used to count from a different sheet. To do this, you must add the sheet name and an exclamation mark before the cell range you are counting. This will allow Excel to search the other sheet for the cells that meet the criteria you have specified. This makes the COUNTIF function a powerful tool for data analysis.
You can use the following basic syntax to use a COUNTIF from another sheet in Excel:
=COUNTIF(Sheet1!A1:B20, ">30")
The following examples show how to use this syntax in practice.
Example 1: COUNTIF From Another Sheet
Suppose we have the following sheet named Sheet1 in Excel that contains some data about basketball players:
Now suppose we’d like to switch to Sheet2 and count the total players who have more than 30 points.
We can use the following syntax to do so:
=COUNTIF(Sheet1!B2:B9, ">30")
Here’s how to apply this formula in practice:
We can see that 2 players have more than 30 points.
Example 2: COUNTIFS From Another Sheet
Suppose we have the following sheet that contains some data about basketball players:
Now suppose we’d like to switch to Sheet2 and count the total players who are on team A and have more than 30 points.
We can use a COUNTIFS function to do so since we’re using multiple criteria when counting:
=COUNTIFS(Sheet1!A2:A9, "A", Sheet1!B2:B9, ">30")
Here’s how to apply this formula in practice:
We can see that 3 players belong to team A and have more than 30 points.