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.

**method**

### 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**.

**method**

### 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**.

**method**

### 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**.