Mehedi Hasan Shimul
The SUMIFS function of Excel is widely used when we want to sum data if criteria are matched. We may want to sum the data from multiple sheets also. In this article, we will show you 3 easy methods to use the SUMIFS function with multiple sheets in Excel.
Download Practice Worksheet
You can download the practice worksheet from here to exercise.
Introduction to Excel SUMIFS Function
The SUMIFS function sums up values based on multiple criteria.
- Syntax:
=SUMIFS( sum_range, criteria_range1, criteria1,criteria_range2, criteria2,…)
- Arguments:
sum_range: The range of values to sum.
criteria_range1: Range for criteria 1 to match.
criteria1: The 1st criteria to match for values to sum.
criteria_range2: Range for criteria 2 to match.
criteria 2: The 2nd criteria to match for values to sum.
We can add more criteria with criteria ranges.
3 Methods to Use SUMIFS Function with Multiple Sheets in Excel
The SUMIFS function can be used to sum values from multiple sheets. Here, we’ll show 3 easy methods to use the SUMIFS function to sum values from multiple sheets. For the example, we have used 3 datasets Collection 1, Collection 2 and Collection 3 from where we want to sum data if our criteria match. The dataset includes some clothing products with their size, color and price. We want to add the price of a product having a specific size and color from different sheets.
Sheet Collection1 contains,
And sheet Collection2,
Again sheet Colection3,
1. Use Excel SUMIFS Function Across Multiple Sheets with AND (+) Operator
At first, we will use the SUMIFS function to sum data from sheets individually. Then adds the values using the AND (+) operator. Let’s follow the steps given below.
- First, we 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)
- Then, we hit Enter.
- Yahoo! We can see the sum from different sheets.
In the formula, 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.
- Similarly, two more criteria are added to the argument.
- We used three SUMIFS functions for three sheets.
- Then added the values by using the AND operator.
Read More: How to Use SUMIFS Formula with Multiple Criteria in Excel (11 Ways)
2. Apply SUMIFS & SUM Functions with Multiple Sheets
We can also find the sum from different sheets using the SUMIFS function and then add their values with the SUM function. We are showing the steps below.
- 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))
- Afterward, press Enter.
- Bravo! We can see the sum of data from multiple sheets if the criteria matched.
Read More: Excel SUMIFS Not Equal to Multiple Criteria (4 Examples)
Similar Readings
- Excel SUMIFS with Multiple Vertical and Horizontal Criteria
- SUMIFS with INDEX-MATCH Formula Including Multiple Criteria
- How to Apply SUMIFS with INDEX MATCH for Multiple Columns and Rows
- Exclude Multiple Criteria in Same Column with SUMIFS Function
- How to Apply SUMIFS with Multiple Criteria in Different Columns
3. Combine SUMIFS, SUMPRODUCT & INDIRECT Functions Altogether in Excel
The SUMPRODUCT function & the INDIRECT function can also be used with the SUMIFS function to sum data from multiple sheets. Let’s follow the steps given below.
- First, 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))
- Now, hit Enter.
- We can see the sum from multiple sheets if the criteria match.
In the formula, 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.
- Similarly two more criteria ranges with criteria are added in the formula.
- The INDIRECT function is used to reference cells with text string from range G11:G13.
Read More: Excel SUMIFS with Multiple Sum Ranges and Multiple Criteria
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 procedure to use the SUMIF function to add values from multiple sheets. We made a dataset having sales of some products for three months.
Sheet Jan (January) contains,
And sheet Feb (February),
Again sheet Mar (March),
Let’s follow the steps given below to sum data from multiple sheets using the SUMIF function.
- First, 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)
- Then, hit Enter.
- Further, use the Fill handle to copy the formula in the cells below.
- We can see the sum of data from multiple sheets with specific criteria.
Conclusion
The SUMIFS function is a widely used function in Excel. In this article, we have shown methods to use the SUMIFS function to sum data from multiple sheets in Excel. Hope, this will help you. Please leave a comment if you have queries or suggestions.
Related Articles
- How to Use SUMIFS with Date Range and Multiple Criteria (7 Quick Ways)
- [Fixed]: SUMIFS Not Working with Multiple Criteria (3 Solutions)
- How to Use SUMIFS When Cells Are Not Equal to Multiple Text
- SUMIFS Sum Range Multiple Columns in Excel(6 Easy Methods)
- How to Use VBA Sumifs with Multiple Criteria in Same Column
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