How to Use SUMIFS Function with Multiple Sheets in Excel

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.

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 criterion to match for values to sum.

criteria_range2: Range for criteria 2 to match.

criteria 2: The 2nd criterion to match for values to sum.

We can add more criteria with criteria ranges.

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

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 add 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 add the values by using the AND operator.

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

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.

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.

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

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.

Download Practice Worksheet

You can download the practice worksheet from here to exercise.

Use SUMIFS with Multiple Sheets.xlsx

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.

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

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Glenn Mar 16, 2023 at 11:20 AM

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.
- Reply
  Shamima Sultana Mar 16, 2023 at 11:24 AM
  
  Dear Glenn,
  
  We are glad to hear that. ExcelDemy is trying hard to give you quality content.
  
  Regards
  Shamima Sultana
  Project Manager | ExcelDemy