This post will guide you how to create 3d references to conditionally sum identical ranges that exist in the multiple sheets, and make it all in only one formula. How do I create a formula to sumif with 3d reference for multiple worksheet based on multiple criteria in excel.
Sumif with 3D Reference for Multiple worksheet
If you want to sum 3d references across the different worksheet, and you need to create a complex formula based on the SUMPRODUCT function, the SUMIF function, and the INDIRECT function.
For example, you have a workbook with four worksheets (summary, sheet1,sheet2,and sheet3), and you want to create a formula in the summary worksheet to sum across the number of sales tab based on two criteria. How to achieve it. Let’s do the following steps:
#1 create a name range “sheets” that contains multiple worksheet names(sheet1,sheet2,sheet3)
#2 switch to summary sheet, and type this formula in Cell B1 to sum the sales across name range.
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"A2:A3"),A2,INDIRECT("'"&sheets&"'!"&"B2:B3")))
#3 press Enter key in your keyboard and then drag the AutoFill handler over other cell to apply this formula.
Related Functions

Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…

Excel SUMIF Function
The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…

Excel INDIRECT function
The Excel INDIRECT function returns the cell reference based on a text string, such as: type the text string “A2” in B1 cell, it just a text string, so you can use INDIRECT function to convert text string as cell reference….
Related Posts
Count Cells that are Case Sensitive
If you are a frequent user of Microsoft Excel, you may have come across situations where you needed to add or count the cells that were casesensitive. If you have done this task manually, I would like to point out ...
Check Dates in chronological order
Assume you have a date list that has different date formats, as seen in the accompanying picture. In this instance, Excel's Sort function will fail to sort them appropriately. However, you may convert all various date formats to a particular ...
Case Sensitive Lookup with SUMPRODUCT and EXACT
Today, we will show you how to use SUMPRODUCT and EXACT to perform a case sensitive exact match. In this article, we provide a simple example to calculate bonus for employees whose names are casesensitive. If you meet similar scenarios ...
Average per Week by Formula in Excel
We usually apply AVERAGE function or relevant functions to return average directly in Excel worksheet. But in some situations, only applying average relevant functions cannot figure out our problem. Sometimes we can create a formula with functions and mathematical operation ...
How to Sum if Equal to X or Y in Excel
In daily work, if we want to sum numbers from a range, and only sum the numbers which being equal to X or Y in the range, we can create a formula with Excel buildin functions to get the result. ...
How To Sum the Largest N Values in Excel
Sometimes we may want to sum the largest N numbers or top N numbers in a range. In this article, we will show you the method of “SUM the Largest N Numbers” by a simple formula which consist of SUMPRODUCT ...
How to Sum for Cell Contains Formula Only in Excel
Sometimes values are created by formulas in cells. If we want to sum values which are created by formulas from a range, but some values which are hardcoded also list in the same range, how can we filter out matched ...
How to Sum the Smallest N Values in Excel
Sometimes we may want to sum the first smallest N numbers in a range in Excel. In this article, we will show you the method of “SUM the Smallest N Numbers” by a simple formula which consist of SUMPRODUCT and ...