COUNTIFS Function in Excel with Multiple Criteria from Different Sheet

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.

COUNTIFS Function in Excel with Multiple Criteria from Different Sheet: 7 Examples

Here, I will describe 7 examples of how to use the 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 scores 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 that 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 that 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 that contain Pass as cell value within the Physics!D5:D15 range.
Finally, the COUNTIFS function will count those cells that 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.

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 that 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.

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 that 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 that 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.

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 that 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 and COUNTIFS functions 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 that 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 them all the 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 that contain text as cell values.
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 that 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.

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 that 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 that are less than the cell value of G8 of the Date worksheet.
Finally, the COUNTIFS function will count those cells that 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.

Download Practice Workbook

You can download the practice workbook from here:

Using COUNTIFS Function.xlsx

Conclusion

I hope you found this article helpful. Here, I have explained 7 suitable examples of using the COUNTIFS function in Excel with multiple criteria from a different sheet. Please, drop comments, suggestions, or queries if you have any in the comment section below.

<< Go Back to Excel COUNTIFS Multiple Criteria | Excel COUNTIFS Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!