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.


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,

how to use sumifs function in excel with multiple sheets

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)

Use Excel SUMIFS Function Across Multiple Sheets with AND (+) Operator

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

Apply SUMIFS & SUM Functions with Multiple Sheets

  • Afterward, press Enter.
  • Bravo! We can see the sum of data from multiple sheets if the criteria matched.

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

Read More: Excel SUMIFS Not Equal to Multiple Criteria (4 Examples)


Similar Readings


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

Combine SUMIFS, SUMPRODUCT & INDIRECT Functions Altogether in Excel

  • 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,

How to Use SUMIF Function with Multiple Sheets in Excel

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.


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

Mehedi Hasan Shimul

Mehedi Hasan Shimul

Hi! I am Mehedi Hasan Shimul. As I am an Engineer solving different problems with the help of Excel amuses me. I write Excel related different problem solving articles here. Hope it will help you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo