# How to Use SUMIFS Function with Multiple Sheets in Excel: 3 Methods

### Method 1 – Use Excel SUMIFS Function Across Multiple Sheets with AND (+) Operator

• Select cell H5 and write the formula given below.
`=SUMIFS('Collection 1'!E5:E14, 'Collection 1'!B5:B14,'Method 1'!G5,'Collection 1'!D5:D14,'Method 1'!G6,'Collection 1'!C5:C14,'Method 1'!G7)+SUMIFS('Collection 2'!E5:E14, 'Collection 2'!B5:B14,'Method 1'!G5,'Collection 2'!D5:D14,'Method 1'!G6,'Collection 2'!C5:C14,'Method 1'!G7)+SUMIFS('Collection 3'!E5:E14, 'Collection 3'!B5:B14,'Method 1'!G5,'Collection 3'!D5:D14,'Method 1'!G6,'Collection 3'!C5:C14,'Method 1'!G7)`

• Hit Enter.
• See the sum from different sheets.

We used the SUMIFS function to sum values from individual sheets and then added the sum values from different sheets with the AND (+) operator. As the arguments of the SUMIFS function

• ‘Collection 1’!E5:E14 is the sum range with sheet reference.
• ‘Collection 1’!B5:B14 is the range for criteria 1 with sheet reference.
• ‘Method 1’!G5 is the criteria 1 with sheet reference.
• Two more criteria are added to the argument.
• We used three SUMIFS functions for three sheets.
• Add the values by using the AND operator.

### Method 2 – Apply SUMIFS & SUM Functions with Multiple Sheets

• Select cell H5 and write the formula given below.
`=SUM(SUMIFS('Collection 1'!E5:E14, 'Collection 1'!B5:B14,'Method 2'!G5,'Collection 1'!D5:D14,'Method 2'!G6,'Collection 1'!C5:C14,'Method 2'!G7),SUMIFS('Collection 2'!E5:E14, 'Collection 2'!B5:B14,'Method 2'!G5,'Collection 2'!D5:D14,'Method 2'!G6,'Collection 2'!C5:C14,'Method 2'!G7),SUMIFS('Collection 3'!E5:E14, 'Collection 3'!B5:B14,'Method 2'!G5,'Collection 3'!D5:D14,'Method 2'!G6,'Collection 3'!C5:C14,'Method 2'!G7))`

• Press Enter.
• See the sum of data from multiple sheets if the criteria match.

Here, we used a similar formula as in Method 1 but we added the values from the SUMIFS function with the SUM function instead of the AND operator.

### Method 3 – Combine SUMIFS, SUMPRODUCT & INDIRECT Functions Altogether in Excel

• Select cell H5 and write down the formula added below.
`=SUMPRODUCT(SUMIFS(INDIRECT("'"&G11:G13&"'!"&"E6:E15"),INDIRECT("'"&G11:G13&"'!"&"B6:B15"),G5,INDIRECT("'"&G11:G13&"'!"&"D6:D15"),G6,INDIRECT("'"&G11:G13&"'!"&"C6:C15"),G7))`

• Hit Enter.
• See the sum from multiple sheets if the criteria match.

We used the SUMPRODUCT function to sum corresponding arrays.

• INDIRECT(“‘”&G11:G13&”‘!”&”E6:E15”) gives the sum range for the SUMIFS function.
• INDIRECT(“‘”&G11:G13&”‘!”&”B6:B15”) is the criteria range 1.
• G5 is the criteria 1.
• Two more criteria ranges with criteria are added to the formula.
• The INDIRECT function references cells with text string from range G11:G13.

## How to Use SUMIF Function with Multiple Sheets in Excel

The SUMIF function works like the SUMIFS function except that it deals with one criterion only. In this section, we will show the SUMIF function’s procedureÂ to add values from multiple sheets. We made a dataset having sales of some products for three months.

Sheet Jan (January) contains,

Sheet Feb (February),

Sheet Mar (March),

The steps below are to sum data from multiple sheets using the SUMIF function.

• Select cell C5 and write the formula given below.
`=SUMIF(Jan!B5:B8,'Use of SUMIF'!B5,Jan!C5:C8)+SUMIF(Feb!B5:B8,'Use of SUMIF'!B5,Feb!C5:C8)+SUMIF(Mar!B5:B8,'Use of SUMIF'!B5,Mar!C5:C8)`

• Hit Enter.

The formula uses the SUMIF function whose arguments are Jan!B5:B8 as the range for criteria(with sheet reference), ‘Use of SUMIF’!B5 is the criteria to match(with sheet reference), Jan!C5:C8 is the sum range if criteria match(including sheet reference).
• Use the Fill handle to copy the formula in the cells below.
• See the sum of data from multiple sheets with specific criteria.

## Related Articles

<< Go Back to Excel SUMIFS with Multiple Criteria | Excel SUMIFS Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

1. I LOOKED AT A LOT OF SITES TO GET THIS FORMULA RIGHT. YOURS WAS THE ONE THAT EXPLAINED IT THE BEST. 100%. IT WORKS NOW.

• Dear Glenn,

We are glad to hear that. ExcelDemy is trying hard to give you quality content.

Regards
Shamima Sultana
Project Manager | ExcelDemy

Advanced Excel Exercises with Solutions PDF