How to SUMIF for Multiple Criteria Across Different Sheet in Excel

Get FREE Advanced Excel Exercises with Solutions!

Excel is the most widely used tool for dealing with massive datasets. If you have data across different sheets and want to sum those data based on multiple criteria, you need to know how to perform SUMIF for multiple criteria in different sheet. In this article, I’ll show you 3 methods by which you will be able to use the SUMIF function for multiple criteria across different sheets.

Here is an overview of this article.

sumif multiple criteria different sheet


How to SUMIF for Multiple Criteria Across Different Sheet in Excel: 3 Methods

Let’s say we have records of quarterly sales for different salesmen in three different sheets named Quarter 1, Quarter 2, and Quarter 3. The salesmen sell two different types of products- Software and Hardware. Now we will calculate the yearly sales of the salesmen who sold software in a sheet named Sales Summary.

Dataset

I will use this dataset and show how you can perform SUMIF for multiple criteria in different sheet.


1. SUMIF Function to Sum from Different Sheets for Multiple Criteria

For applying multiple criteria with the SUMIF function, you have to make a list based on one criterion in the sheet where you will make the calculation. For our dataset, we made the list of salesmen who sold software in the Sales Summary sheet.

Steps:

  • Go to C9 and write down the following formula.
=SUMIF('Quarter 1'!B5:B11,'Sales Summary'!B9,'Quarter 1'!D5:D11)+SUMIF('Quarter 2'!B5:B11,'Sales Summary'!B9,'Quarter 2'!D5:D11)+SUMIF('Quarter 3'!B5:B11,'Sales Summary'!B9,'Quarter 3'!D5:D11)
  • Then, press ENTER to get the output.

SUMIF sumif multiple criteria different sheet

Formula Explanation

  • Quarter 1′!B5:B11 is the range for criteria from Quarter 1.
  • Sales Summary’!B9 is the criteria from the Sales Summary.
  • Quarter 1′!D5:D11 is the range for value from the Quarter 1 sheet.
  • In the same way, the SUMIF function is applied to the other two sheets.
  • Drag cell C9 to get the summation of the three quarters’ sales for all other salesmen who sold Software.

AutoFill sumif multiple criteria different sheet

Read More: SUMIF Across Multiple Sheets in Excel


2. SUMIFS Function to Sum from Different Sheets for Multiple Criteria

The SUMIFS function allows us to summate different sheets based on multiple criteria.  Now we will use the SUMIFS function for our dataset to make a summation of different quarter sales for salesmen who sold Software in a sheet named Sales Summary-2.

Steps:

  • Move to C9 and write down the following formula.
=SUMIFS('Quarter 1'!D5:D11,'Quarter 1'!B5:B11,'Sales Summary-2'!B9,'Quarter 1'!C5:C11,'Sales Summary-2'!$C$6)+ SUMIFS('Quarter 2'!D5:D11,'Quarter 2'!B5:B11,'Sales Summary-2'!B9,'Quarter 2'!C5:C11,'Sales Summary-2'!$C$6)+SUMIFS('Quarter 3'!D5:D11,'Quarter 3'!B5:B11,'Sales Summary-2'!B9,'Quarter 3'!C5:C11,'Sales Summary-2'!$C$6)
  • After that, press ENTER to get the output.

SUMIFS multiple criteria different sheet

Formula Explanation

  • Here, Quarter 1′!D5:D11 is the range for the sum in the Quarter 1 sheet,
  • Quarter 1′!B5:B11 is the range for criteria 1 in the Quarter 1 sheet,
  • Sales Summary-2′!B9 is the first criteria from the Sales Summary-2 sheet,
  • Quarter 1′!C5:C11 is the range for criteria 2 in the Quarter 1 sheet,
  • ‘Sales Summary-2’!$C$6 is the second criterion from the Sales Summary-2,
  • In a similar way, SUMIFS is applied for all other two sheets.

Output

Read More: SUMIF Multiple Ranges


3. Using SUMPRODUCT SUMIFS and INDIRECT Functions

Without repeating SUMIF or SUMIFS multiple times, you can use a combination of the SUMPRODUCT, SUMIFS, and INDIRECT functions altogether to get the same result.

Steps:

  • First, we will insert the name of the sheets from where the value of the quarterly sales will be obtained in a sheet named Sales Summary-3.
  • Type the formula in cell C9.
=SUMPRODUCT(SUMIFS(INDIRECT("'"&E$9:$E$11&"'!$D$5:$D$11"),INDIRECT("'"&$E$9:$E$11&"'!$B$5:$B$11"),B9,INDIRECT("'"&$E$9:$E$11&"'!$C$5:$C$11"),$C$6))
  • Then, press ENTER to get the output.

Combination

Formula Breakdown

  • “‘”&E$9:$E$11&”‘!$D$5:$D$11” → This makes the references for Sales.
    • Output: {“‘Quarter 1’!$D$5:$D$11″;”‘Quarter 2’!$D$5:$D$11″;”‘Quarter 3’!$D$5:$D$11”}
  • “‘”&$E$9:$E$11&”‘!$B$5:$B$11” → This makes the references for Salesmen.
    • Output: {“‘Quarter 1’!$B$5:$B$11″;”‘Quarter 2’!$B$5:$B$11″;”‘Quarter 3’!$B$5:$B$11”}
  • “‘”&$E$9:$E$11&”‘!$C$5:$C$11” → This makes the references for Product Category.
    • Output: {“‘Quarter 1’!$C$5:$C$11″;”‘Quarter 2’!$C$5:$C$11″;”‘Quarter 3’!$C$5:$C$11”}
  • INDIRECT(“‘”&$E$9:$E$11&”‘!$B$5:$B$11”) → This is the criteria_range1.
    • Output: {“Antony”;”Antony”;”Antony”}
  • INDIRECT(“‘”&$E$9:$E$11&”‘!$C$5:$C$11”) → This is the criteria_range2
    • Output: {“Hardware”;”Hardware”;”Hardware”}
  • SUMPRODUCT(SUMIFS(INDIRECT(“‘”&E$9:$E$11&”‘!$D$5:$D$11”),INDIRECT(“‘”&$E$9:$E$11&”‘!$B$5:$B$11”),B9,INDIRECT(“‘”&$E$9:$E$11&”‘!$C$5:$C$11”),$C$6)) →  This becomes,
  • SUMPRODUCT({86143;87371;108292})
    • Output: 281806
  • Finally, AutoFill up to C12.

Combination


Download Practice Workbook


Conclusion

Though you can perform SUMIF for multiple criteria in different sheet by all three methods, Method 3 (combination of SUMPRODUCT and INDIRECT functions) is more convenient as you don’t need to insert all the sheets differently in the formula. If you face any confusion, please leave a comment.


Further Readings


<< Go Back to SUMIF Multiple Criteria | Excel SUMIF Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo