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.
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.
- Use the Fill handle to copy the formula in the cells below.
- See the sum of data from multiple sheets with specific criteria.
Download Practice Worksheet
You can download the practice worksheet from here to exercise.
Related Articles
- Excel SUMIFS Not Equal to Multiple Criteria
- How to Use SUMIFS Function with Wildcard in Excel
- [Fixed]: SUMIFS Not Working with Multiple Criteria
<< Go Back to Excel SUMIFS with Multiple Criteria | Excel SUMIFS Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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