Musiha Mahfuza Mukta
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.
