# SUMIF for Multiple Criteria Across Different Sheets in Excel (3 Methods)

If you have data across different sheets and want to sum those data based on multiple criteria, you need to know some techniques. 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.

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.

## 3 Methods to Use Sumif for Multiple Criteria Across Different Sheets

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

Now to use the SUMIF function for adding up values from different sheets, type the following formula in an empty cell (B8) in the sheet where you want to make the calculation (Sales Summary),

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

Here, Quarter 1′!B5:B1 is the range for criteria from Quarter 1 sheet, Sales Summary’!A8 is the criteria from Sales Summary sheet, and Quarter 1′!D5:D11 is the range for value from Quarter 1 sheet. In the same way, the SUMIF function is applied for the other two sheets.

Press ENTER and you will get the summation of sales from all of the sheets for mark who sold Software.

Drag cell B8 to get the summation of the three quarters’ sales for all other salesmen who sold Software.

Remember you can not use multiple criteria in the argument of the SUMIF function. To get a summation by using the SUMIF function based on multiple criteria you have to customize your dataset.

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

The SUMIFS function allows us to make summations from 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.

Type the following formula in cell B8 of the Sales Summary-2 sheet,

`=SUMIFS('Quarter 1'!D5:D11,'Quarter 1'!B5:B11,'Sales Summary-2'!A8,'Quarter 1'!C5:C11,'Sales Summary-2'!\$B\$5)+ SUMIFS('Quarter 2'!D5:D11,'Quarter 2'!B5:B11,'Sales Summary-2'!A8,'Quarter 2'!C5:C11,'Sales Summary-2'!\$B\$5)+SUMIFS('Quarter 3'!D5:D11,'Quarter 3'!B5:B11,'Sales Summary-2'!A8,'Quarter 3'!C5:C11,'Sales Summary-2'!\$B\$5)`

Here, Quarter 1′!D5:D11 is the range for the sum in Quarter 1 sheet, Quarter 1′!B5:B11 is the range for criteria 1 in Quarter 1 sheet, Sales Summary-2′!A8 is the first criteria from the Sales Summary-2 sheet, Quarter 1′!C5:C11 is the range for criteria 2 in Quarter 1 sheet and ‘Sales Summary-2’!\$B\$5 is the second criteria from the Sales Summary-2 sheet. In a similar way, SUMIFS is applied for all other two sheets.

Press ENTER to find the summation of three quarters’ sales for the first salesman who sold Software.

Drag cell B8 to get the summations for all other salesmen who sold Software.

Read More: SUMIF Multiple Ranges [6 Useful Ways]

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

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 cell B8 in the Sales Summary sheet,

`=SUMPRODUCT(SUMIFS(INDIRECT("'"&D\$8:\$D\$10&"'!\$D\$5:\$D\$11"),INDIRECT("'"&\$D\$8:\$D\$10&"'!\$B\$5:\$B\$11"),A8,INDIRECT("'"&\$D\$8:\$D\$10&"'!\$C\$5:\$C\$11"),\$B\$5))`

Here D\$8:\$D\$10 is the name of different sheets, D\$5:\$D\$11 is the range for sum value( Sales), \$B\$5:\$B\$11 is the range for first criteria(Salesman), A8 is the first criteria (Mark), C\$5:\$C\$11 is the range for second criteria (Product) and \$B\$5 is the second criteria (Software).

Press ENTER to find the summation of three quarters’ sales for the first salesman who sold Software.

Drag the cell B8 to get the summations for all other salesmen who sold Software

## Conclusion

Though you can get summation from different sheets for multiple criteria by all three methods, Method-3 (SUMPRODUCTINDIRECT 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.