Md. Ahaduzzaman
Sometimes we have to work with a really big dataset. Data range can be in different data sheets in an Excel file. If we want to sum multiple data range from different data sheet based on some fixed criteria, this article will come in handy. Here we are going to learn 3 different methods to SUMIF multiple criteria from the different sheet.
📁 Download Excel File
Download the practice file from here.
Learn to Use SUMIF with Multiple Criteria for Different Sheet with These 3 Methods
Here we are using a dataset with data entry of two different datasheets named Half Yearly and Yearly. Let’s assume we are trying to know the total number of the students of class 9 who have 2 different number records in 2 different sheets. We are going to use three different functions to calculate their total number. The snapshot of our sample dataset is as follows.
1. SUM from Different Sheets with Multiple Criteria Using SUMIF Function
Here we are going to use SUMIF Function to SUMIF multiple criteria in a different sheet. So the step-by-step procedures are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- At first in cell C8, we write this formula in the formula box.
=SUMIF(‘Half yearly’!B6:B10,’Sumif function’!B8,’Half yearly’!D6:D10)+SUMIF(Yearly!B6:B10,’Sumif function’!B8,Yearly!D6:D10)
🔨 Formula Breakdown
👉 Firstly here Sumif function’!B8 is the criteria.
👉 Here Half yearly’!B6:B10 is the range with which the criteria is checked.
👉 Finally the SUMIF Function gives the value if the criteria match. We add them to get the final result.
- Now we use the Fill Handle tool.
- We drag the formula from cell C8 to C10.
- Finally, we will get the total mark of the students in column C.
2. SUM from Different Sheets with Multiple Criteria Using SUMIFS Function
Here we will see how to apply SUMIFS Function to SUMIF multiple criteria in a different sheet. Therefore the step-by-step procedures are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- At first, we write this formula in the formula box of cell C8.
=SUMIFS(‘Half yearly’!D6:D10,’Half yearly’!B6:B10,’Sumifs function’!B8,‘Half yearly’!C6:C10,’Sumifs function’!$C$5)+SUMIFS(Yearly!D6:D10,Yearly!B6:B10,’Sumifs function’!B8,Yearly!C6:C10,’Sumifs function’!$C$5)
🔨 Formula Breakdown
👉 Firstly here is the ‘Half yearly’!D6:D10 is our sum range in the first sheet.
👉 ‘Half yearly’!B6:B10 is our first range for the criteria.
👉 ‘Sumifs function’!B8 is the criteria that are checked in the criteria range.
👉 ‘Half yearly’!C6:C10 is our second range for criteria.
👉 ‘Sumifs function’!$C$5 is the second criterion that checks in the criteria range.
👉 SUMIFS Function gives us the result from the sum range if all the criteria match.
👉 Finally We add the value from two different sheets to get our result.
- After this step, we again use the Fill Handle tool.
- Following that we drag the formula from cell C8 to cell C10.
- Finally, we will get our desired result in column C.
3. Applying SUMPRODUCT, SUMIFS, and INDIRECT Functions Combined
This section will show how to effectively use SUMPRODUCT, SUMIFS & INDIRECT Functions to SUMIF multiple criteria in a different sheet. So the steps are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly in this step, we write this formula in the formula box of cell C8.
=SUMPRODUCT(SUMIFS(INDIRECT(“‘”&E$8:$E$9&”‘!$D$6:$D$10”),INDIRECT(“‘”&$E$8:$E$9&”‘!$B$6:$B$10”), B8,INDIRECT(“‘”&$E$8:$E$9&”‘!$C$6:$C$10”),$C$5))
🔨 Formula Breakdown
👉 At first here “‘”&E$8:$E$9 is the reference text and “‘!$D$6:$D$10” is the array for reference.
👉 INDIRECT(“‘”&E$8:$E$9&”‘!$D$6:$D$10”) is the sum range.
👉 INDIRECT(“‘”&$E$8:$E$9&”‘!$B$6:$B$10”) is the criteria range for first criteria.
👉 B8 is the first criterion that is checked in the criteria range.
👉 INDIRECT(“‘”&$E$8:$E$9&”‘!$C$6:$C$10”) is the second range of criteria.
👉 $C$5 is the second criterion for the second criteria range.
👉 If all criteria match SUMIFS Function gives us the result from the sum range.
👉 Finally SUMPRODUCT Function gives us the summation of the ranges from the sum range.
- Following that like the previous methods, we use the Fill Handle tool.
- Following that we drag the formula from cell C8 to C10.
- Finally, we will get our result in column C.
How to Use SUMIF Function for Multiple Criteria in Different Columns
Here we will see how to use SUMIF Function properly for multiple criteria in different columns. So the step-by-step procedures for doing this are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- At first in the formula box, we write this formula in cell C13 and press Enter button.
=SUMIF(B6:B10,C12,D6:D10)+SUMIF(B6:B10,C12,E6:E10)
🔨 Formula Breakdown
👉 Firstly here C12 is the criteria that are checked in data range B6:B10.
👉 After that if the criteria match, SUMIF Function gives us the corresponding cell value from D6:D10.
👉 Finally we add the value from two different sheets to get our result.
- We will get the total mark of Robin in cell C13.
How to Use SUMIF and VLOOKUP Functions for Multiple Sheets
Here we are going to learn how to use SUMIF and VLOOKUP Functions for multiple sheets. Therefore the brief description of how to do this properly is as follows.
⬇️⬇️ STEPS ⬇️⬇️
- At first in the formula box of cell C13, we write this formula and press Enter.
=SUMIF(‘Half yearly’!B6:B10,VLOOKUP(C12,B6:B10,1,FALSE),’Half yearly’!D6:D10)+SUMIF(Yearly!B6:B10,VLOOKUP(C12,B6:B10,1,FALSE),Yearly!D6:D10)
🔨 Formula Breakdown
👉 Firstly here VLOOKUP(C12,B6:B10,1,FALSE) is our criteria.
👉 Following that VLOOKUP Function looks for the exact match for cell C12 in the data range B6:B10.
👉 After getting the criteria to match it then checks the criteria in Half yearly’!B6:B10 data range.
👉 If the criteria match SUMIF Function gives the result from the data range Half yearly’!D6:D10.
👉 Finally we add up the value from two different sheets to get our result.
- Finally, in cell C13 we will get our desired result.
📄 Important Notes
🖊️ While using the Absolute Cell Reference we have to be careful that we use them properly.
🖊️ Also we need to carefully write all the arguments of the function properly.
📝 Takeaways From This Article
📌 We can use three methods to SUMIF multiple criteria from different sheets.
📌 We can also use VLOOKUP Function to SUMIF multiple criteria from different sheets.
Conclusion
Here we have learned three different methods to SUMIF multiple criteria in different sheets. We can use SUMIF, SUMIFS, or SUMPRODUCT SUMIFS & INDIRECT functions for this. Users can use whatever they like. Please comment in the comment section if you have any queries regarding this article. The writer will try his best to solve it. For further queries and Excel-related problems, you can visit Excelden.
