**Excel** is the most widely used tool for dealing with massive datasets. If you have data across different sheets and want to sum those data based on multiple criteria, you need to know how to perform **SUMIF** for multiple criteria in different sheet. In this article, I’ll show you **3** methods by which you will be able to use **the** **SUMIF function for multiple criteria** across different sheets.

Here is an overview of this article.

**Table of Contents**hide

**Download Practice Workbook**

**3 Methods to Use SUMIF for Multiple Criteria Across Different Sheets**

Let’s say we have records of Quarterly sales for different salesmen in three different sheets named *Quarter 1, Quarter 2, *and *Quarter 3**.* The salesmen sell two different types of products- **Software** and **Hardware**. Now we will calculate the yearly sales of the salesmen who sold software in a sheet named *Sales Summary**.*

I will use this dataset and show how you can perform **SUMIF** for multiple criteria in different sheet.

**1. SUMIF Function to Sum from Different Sheets for Multiple Criteria**

For applying multiple criteria first you have to make a list based on one criterion in the sheet where you will make the calculation. For our dataset, we made the list of salesmen who sold software in the *Sales Summary *sheet.

__Steps:__

- Go to
**C9**and write down the following formula

`=SUMIF('Quarter 1'!B5:B11,'Sales Summary'!B9,'Quarter 1'!D5:D11)+SUMIF('Quarter 2'!B5:B11,'Sales Summary'!B9,'Quarter 2'!D5:D11)+SUMIF('Quarter 3'!B5:B11,'Sales Summary'!B9,'Quarter 3'!D5:D11)`

- Then, press
**ENTER**to get the output.

__Formula Explanation__

**Quarter 1′!B5:B11**is the range for criteria from*Quarter 1***Sales Summary’!B9**is the criteria from the*Sales Summary***Quarter 1′!D5:D11**is the range for value from thesheet*Quarter 1*- In the same way,
**the SUMIF function**is applied to the other two sheets.

- Now, Drag cell
**C9**to get the summation of the three quarters’ sales for all other salesmen who sold**Software**.

**Read More:** **SUMIF Across Multiple Sheets in Excel (3 Methods)**

**2. SUMIFS Function to Sum from Different Sheets for Multiple Criteria**

**The SUMIFS function** allows us to summate different sheets based on **multiple criteria**. Now we will use **the SUMIFS function** for our dataset to make a summation of different quarter sales for salesmen who sold Software in a sheet named *Sales Summary-2.*

__Steps:__

- First of all, go to
**C9**and write down the following formula

`=SUMIFS('Quarter 1'!D5:D11,'Quarter 1'!B5:B11,'Sales Summary-2'!B9,'Quarter 1'!C5:C11,'Sales Summary-2'!$C$6)+ SUMIFS('Quarter 2'!D5:D11,'Quarter 2'!B5:B11,'Sales Summary-2'!B9,'Quarter 2'!C5:C11,'Sales Summary-2'!$C$6)+SUMIFS('Quarter 3'!D5:D11,'Quarter 3'!B5:B11,'Sales Summary-2'!B9,'Quarter 3'!C5:C11,'Sales Summary-2'!$C$6)`

- After that, press
**ENTER**to get the output.

**Formula Explanation**

- Here,
**Quarter 1′!D5:D11**is the range for the sum in thesheet,*Quarter 1* **Quarter 1′!B5:B11**is the range for criteria 1 in thesheet,*Quarter 1***Sales Summary-2′!B9**is the first criteria from thesheet,*Sales Summary-2***Quarter 1′!C5:C11**is the range for criteria 2 in thesheet*Quarter 1***‘Sales Summary-2’!$C$6**is the second criterion from the*Sales Summary-2*- In a similar way,
**SUMIFS**is applied for all other two sheets.

- Then, use the
**Fill Handle**to**AutoFill**up to**C12**.

**Read More:** **SUMIF Multiple Ranges [6 Useful Ways]**

**Similar Readings**

**Sum Multiple Columns Based on Multiple Criteria in Excel****Sum Based on Column and Row Criteria in Excel (7 Handy Ways)****How to SUM with IF Condition in Excel (6 Suitable Examples)**

**3. Using SUMPRODUCT SUMIFS and INDIRECT Functions**

Without repeating **SUMIF **or **SUMIFS **multiple times, you can use **the SUMPRODUCT function**, the **SUMIFS **function, and **the INDIRECT function** altogether to get the same result.

__Steps:__

- First, we will insert the name of the sheets from where the value of the quarterly sales will be obtained in a sheet named
*Sales Summary-3.* - Now type the formula in the cell
**C9.**

`=SUMPRODUCT(SUMIFS(INDIRECT("'"&E$9:$E$11&"'!$D$5:$D$11"),INDIRECT("'"&$E$9:$E$11&"'!$B$5:$B$11"),B9,INDIRECT("'"&$E$9:$E$11&"'!$C$5:$C$11"),$C$6))`

- Then, press
**ENTER**to get the output.

**Formula Breakdown**

**“‘”&E$9:$E$11&”‘!$D$5:$D$11”**→ This makes the references for Sales.**Output:**{“‘Quarter 1’!$D$5:$D$11″;”‘Quarter 2’!$D$5:$D$11″;”‘Quarter 3’!$D$5:$D$11”}

**“‘”&$E$9:$E$11&”‘!$B$5:$B$11”**→ This makes the references for Salesmen.**Output:**{“‘Quarter 1’!$B$5:$B$11″;”‘Quarter 2’!$B$5:$B$11″;”‘Quarter 3’!$B$5:$B$11”}

**“‘”&$E$9:$E$11&”‘!$C$5:$C$11”**→ This makes the references for Product Category.**Output:**{“‘Quarter 1’!$C$5:$C$11″;”‘Quarter 2’!$C$5:$C$11″;”‘Quarter 3’!$C$5:$C$11”}

**INDIRECT(“‘”&$E$9:$E$11&”‘!$B$5:$B$11”)**→ This is the criteria_range1.**Output:**{“Antony”;”Antony”;”Antony”}

**INDIRECT(“‘”&$E$9:$E$11&”‘!$C$5:$C$11”)**→ This is the criteria_range2**Output:**{“Hardware”;”Hardware”;”Hardware”}

**SUMPRODUCT(SUMIFS(INDIRECT(“‘”&E$9:$E$11&”‘!$D$5:$D$11”),INDIRECT(“‘”&$E$9:$E$11&”‘!$B$5:$B$11”),B9,INDIRECT(“‘”&$E$9:$E$11&”‘!$C$5:$C$11”),$C$6))**→ This becomes,**SUMPRODUCT({86143;87371;108292})****Output:**281806

- Finally,
**AutoFill**up to**C12**.

**Conclusion**

Though you can perform **SUMIF** for multiple criteria in different sheet by all three methods, **Method 3** (**SUMPRODUCT** – **INDIRECT** combination) is more convenient as you don’t need to insert all the sheets differently in the formula. If you face any confusion, please leave a comment.