Sometimes you may need to use **the COUNTIFS function** with multiple criteria from a different sheet in Excel. So, if you are looking for the use of a **COUNTIFS function with multiple criteria** from a different sheet in Excel then you have come to the right place. In this article, I will demonstrate how to use the **COUNTIFS** function with multiple criteria from a different sheet in Excel.

## Download Practice Workbook

You can download the practice workbook from here:

## 7 Examples to Use COUNTIFS Function in Excel with Multiple Criteria from Different Sheet

Here, I will describe **7 examples** of how to use a **COUNTIFS** function with multiple criteria from a different sheet in Excel. In addition, for your better understanding, I’m going to use some sample datasets.

Moreover, there are **five** different worksheets with **five **individual datasets. The names of the worksheets are **Math, English, Physics, Student Info, **and **Missing Info**. Additionally, the worksheets named **Math, English,** and** Physics** contain the score of the students.

On the other hand, the worksheets named **Student Info** and **Missing Info **contain some additional information about the students.

### 1. Using Multiple Criteria in COUNTIFS Function from Different Sheet

Here, I will use multiple criteria in the **COUNTIFS** function from different sheets in Excel. Suppose, I want to find out how many students named **Jack** from **Alberta** have passed in **Physics**. The steps are given below.

**Steps:**

- Firstly, you may go to a
**different worksheet**named**Multiple Criteria**. - Secondly, you have to select a new cell
**G9**where you want to keep the result. - Thirdly, you should use the formula given below in the
**G9**cell.

`=COUNTIFS(B5:B15,'Student Info'!B8,C5:C15,"Alberta",Physics!D5:D15,"Pass")`

**Formula Breakdown**

Here, the** COUNTIFS** function will count those cells which will fulfill the given conditions.

- Firstly,
**B5:B15**is the**1st**criteria range. This means the cell values of the**B column**within the worksheet are named**Multiple Criteria**. - Secondly,
**‘Student Info’!B8**is the particular criteria. Which denotes the cell value of**B8**from the worksheet named**Student Info**. Here, the cell value is**Jack**. So, the**COUNTIFS**function will search for those cells which contain**Jack**as a cell value. - Thirdly,
**C5:C15**denotes the**2nd**criteria range. This means the cell values of the**C column**within the worksheet are named**Multiple Criteria**. - Fourthly,
**“Alberta”**is the given criteria. This means the**COUNTIFS**function will search for those cells which contain**Alberta**as a cell value. - Similarly,
**Physics!D5:D15**denotes the**3rd**criteria range. Which refers to the cell values of the**D column**from the worksheet named**Physics**. - And,
**“Pass”**is the given criteria. This means the**COUNTIFS**function will search for those cells which contain**Pass**as cell value within the**Physics!D5:D15**range. - Finally, the
**COUNTIFS**function will count those cells which fulfill all those criteria.

- Subsequently, press
**ENTER**to get the result.

Finally, you will get how many students have passed in **Physics** having the name** Jack** and from the **Alberta** region.

**Read More: COUNTIFS to Count Across Multiple Columns in Excel**

### 2. Employing Same Criteria Twice from Different Sheet in Excel

Here, you may employ similar criteria in the **COUNTIFS** function from a different sheet in Excel. Suppose, I want to find out how many students have passed all subjects. The steps are given below.

**Steps:**

- Firstly, you may go to a different worksheet named
**Same Criteria**. - Secondly, you have to select a new cell
**D17**where you want to keep the result. - Thirdly, you should use the formula given below in the
**D17**cell.

`=COUNTIFS(Math!D5:D15,"Pass",English!D5:D15,"Pass",Physics!D5:D15,"Pass")`

**Formula Breakdown**

Here, the** COUNTIFS** function will count those cells which will fulfill the given conditions.

- Firstly,
**Math!D5:D15**is the**1st**criteria range. Which denotes the cell values of the**D column**from the worksheet named**Math**. - Secondly,
**English!D5:D15**denotes the**2nd**criteria range. Which refers to the cell values of the**D column**of the worksheet named**English**. - Thirdly,
**Physics!D5:D15**denotes the**3rd**criteria range. Which refers to the cell values of the**D column**from the worksheet named**Physics**. - Here,
**“Pass”**is the given criteria for all criteria ranges. This means the**COUNTIFS**function will count those cells which contain**Pass**as cell value simultaneously in the given**3**ranges.

- Subsequently, press
**ENTER**to get the result.

Finally, you will get how many students have passed all subjects.

**Read More: Excel COUNTIFS Not Working with Multiple Criteria (2 Solutions)**

### 3. Use of OR Logic in COUNTIFS Function with Multiple Criteria from Different Sheet

Here, you may use the **OR logic in the COUNTIFS function from different sheets in Excel**. Suppose, I want to find out how many students have passed **Physics** or **Math**. The steps are given below.

**Steps:**

- Firstly, you may go to a different worksheet named
**OR Logic**. - Secondly, you have to select a new cell
**D17**where you want to keep the result. - Thirdly, you should use the formula given below in the
**D17**cell.

`=COUNTIFS(Physics!D5:D15,"Pass")+COUNTIFS(Math!D5:D15,"Pass")`

**Formula Breakdown**

Here, the** COUNTIFS** function will count those cells which will fulfill the given conditions.

- Firstly,
**Physics!D5:D15**denotes the criteria range. Which refers to the cell values of the**D column**from the worksheet named**Physics**. - Secondly,
**“Pass”**is the given criteria. This means the**COUNTIFS**function will count those cells which contain**Pass**as cell value.**COUNTIFS(Physics!D5:D15,”Pass”)—>**turns**8**.

- Similarly,
**Math!D5:D15**is the criteria range. Which denotes the cell values of the**D column**from the worksheet named**Math**. - Here,
**“Pass”**is the given criteria which means the**COUNTIFS**function will count those cells which contain**Pass**as cell value.**COUNTIFS(Math!D5:D15,”Pass”)—>**gives**7**.

- Lastly, the
**Plus sign (+)**will add those results.**Output: 15**.

- Subsequently, press
**ENTER**to get the result.

Finally, you will get how many students have passed in** either Physics or Math**.

**Read More: Excel COUNTIFS Function with Multiple Criteria in Same Column**

### 4. Count Cells Between Numbers with Multiple Criteria in Excel from Different Sheet

Here, you also can count the cells using only numbers as multiple criteria in the **COUNTIFS** function from a different sheet in Excel. Suppose, I want to find out how many students have got marks of more than **80** in** Math, **more than **60** in** English, **and more than **70 **in **Physics**. The steps are given below.

**Steps:**

- Firstly, you may go to a different worksheet named
**Numbers**. - Secondly, you have to select a new cell
**G9**where you want to keep the result. - Thirdly, you should use the formula given below in the
**G9**cell.

`=COUNTIFS(Math!C5:C15,">80",English!C5:C15,">60",Physics!C5:C15,">70")`

**Formula Breakdown**

Here, the** COUNTIFS** function will count those cells which will fulfill the given conditions.

- Firstly,
**Math!C5:C15**is the**1st**criteria range. Which denotes the cell values of the**C column**from the worksheet named**Math**. - Secondly,
**“>80”**is the particular criteria. Which checks whose values are greater than**80**. - Thirdly,
**English!C5:C15**denotes the**2nd**criteria range. Which refers to the cell values of the**C column**of the worksheet named**English**. - Fourthly,
**“>60”**is the particular criteria. Which checks whose values are greater than**60**. - Similarly,
**Physics!C5:C15**denotes the**3rd**criteria range. Which refers to the cell values of the**C column**from the worksheet named**Physics**. - Here,
**“>70”**is the particular criteria. Which checks whose values are greater than**70**. - Finally, the
**COUNTIFS**function will count those cells which fulfill all those criteria.

- Subsequently, press
**ENTER**to get the result.

Finally, you will see how many students have got marks of more than **80** in** Math, **more than **60** in** English, **and more than **70 **in **Physics**.

### 5. Use of SUM & COUNTIFS Functions with Multiple Criteria

You can apply **the** **SUM function** and the **COUNTIFS** function with multiple criteria using different sheets in Excel. Now, let’s find out how many students have got marks more than or equal to **80** in all subjects. The steps are given below.

**Steps:**

- Firstly, you may go to a different worksheet named
**SUM**. - Secondly, you have to select a new cell
**G10**where you want to keep the result. - Thirdly, you should use the formula given below in the
**G10**cell.

`=SUM(COUNTIFS(B5:B15,{"Jack","Michel","Bruce"},Math!C5:C15,">=80",English!C5:C15,">=80",Physics!C5:C15,">=80"))`

**Formula Breakdown**

Here, the** COUNTIFS** function will count those cells which will fulfill the given conditions.

- Firstly,
**B5:B15**is the**1st**criteria range. This means the cell values of the**B column**within the worksheet are named**SUM**. - Secondly,
**{“Jack”,”Michel”,”Bruce”}**denotes the criteria.*Here, the***second bracket**will give the result in**Matrix**form. - Thirdly,
**Math!C5:C15**is the**2nd**criteria range. Which denotes the cell values of the**C column**from the worksheet named**Math**. - Fourthly,
**English!C5:C15**denotes the**3rd**criteria range. Which refers to the cell values of the**C column**of the worksheet named**English**. - Similarly,
**Physics!C5:C15**denotes the**4th**criteria range. Which refers to the cell values of the**C column**from the worksheet named**Physics**. - And,
**“>=80”**is the particular criteria for all those criteria ranges. Which checks whose values are greater than or equal to**80**. - Finally, the
**COUNTIFS**function will count those cells which fulfill all those criteria.**Output: {1,1,0}.**

- Now, the
**SUM**function will add them.**Output: 2**.

- Subsequently, press
**ENTER**to get the result.

Finally, you will see how many students have got marks more than or equal to **80** in all **three **subjects.

### 6. Using Wildcard Characters to Count Missing Information

You can apply the **Wildcard** characters in the **COUNTIFS** function with multiple criteria from a different sheet in Excel. Now, let’s find out how many students have given their all information. The steps are given below.

**Steps:**

- Firstly, you may go to a different worksheet named
**Wild**. - Secondly, you have to select a new cell
**C17**where you want to keep the result. - Thirdly, you should use the formula given below in the
**C17**cell.

`=COUNTIFS(B5:B15,"*",'Missing Info'!B5:B15,"<>"&"")`

**Formula Breakdown**

Here, the** COUNTIFS** function will count those cells which will fulfill the given conditions.

- Firstly,
**B5:B15**is the**1st**criteria range. This means the cell values of the**B column**within the worksheet are named**Wild**. - Secondly,
**“*” (Asterisk)**is the particular criteria. Which denotes any cell with**a text**value. So, the**COUNTIFS**function will search for those cells which contain**text**as cell value. - Thirdly,
**‘Missing Info’!B5:B15**denotes the**2nd**criteria range. This means the cell values of the**B column**within the worksheet are named**Missing Info**. - Fourthly,
**“<>”&””**is the given criteria. Which will check whether the cell contains any value or is blank. - Finally, the
**COUNTIFS**function will count those cells which have any text as cell value in the**Region**column of the**Wild**worksheet and simultaneously have any value in the**Name**column of the**Missing Info**worksheet.

- Subsequently, press
**ENTER**to get the result.

Finally, you will get how many students have given their all information.

**Read More:** **Excel COUNTIFS with Multiple Criteria Including Not Blank**

### 7. Counting Dates Between Two Dates with Multiple Criteria from Different Sheet

Here, you also can count the cells using only **dates** as multiple criteria in the **COUNTIFS** function from a different sheet in Excel. Now, suppose, I want to find out how many students have been admitted between **9/2/2020 **and** 9/5/2021**. The steps are given below.

**Steps:**

- Firstly, you may go to a different worksheet named
**Date**. - Secondly, you have to select a new cell
**G9**where you want to keep the result. - Thirdly, you should use the formula given below in the
**G9**cell.

`=COUNTIFS('Student Info'!E5:E15,">"&G7,'Student Info'!E5:E15,"<"&Date!G8)`

**Formula Breakdown**

Here, the** COUNTIFS** function will count those cells which will fulfill the given conditions.

- Firstly,
**‘Student Info’!E5:E15**is the**1st**criteria range. This means the cell values of the**E column**of the worksheet are named**Student Info**. - Secondly,
**“>”&G7**are the particular criteria. Which denotes the**COUNTIFS**function will search for those cells which are greater than the cell value of**G7**. - Thirdly,
**‘Student Info’!E5:E15**denotes the**2nd**criteria range. This means the cell values of the**E column**of the worksheet are named**Student Info.** - Fourthly,
**“<“&Date!G8**is the**2nd**criterion. This means the**COUNTIFS**function will search for those cells which are less than the cell value of**G8**of**the Date**worksheet. - Finally, the
**COUNTIFS**function will count those cells which fulfill all those criteria.

- Subsequently, press
**ENTER**to get the result.

Finally, you will get how many students have been admitted between **9/2/2020 **and** 9/5/2021**.

## Practice Section

Now, you can practice the explained method by yourself.

## Conclusion

I hope you found this article helpful. Here, I have explained **7 **suitable examples to use the **COUNTIFS **function in Excel with multiple criteria from a different sheet. You can visit our website **Exceldemy** to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.