To conditionally sum identical ranges that exist in separate worksheets, all in one formula, you can use the SUMIF function with INDIRECT, wrapped in SUMPRODUCT. In the example shown, the formula in C9 is:
The data on each of the three sheets being processed looks like this:
First of all, note that you can't use SUMIFs with a "normal" 3D reference like this:
This is the standard "3D syntax" but if you try to use it with SUMIF, you'll get a #VALUE error. So, to workaround this problem you can use a named range "sheets" that lists each sheet (worksheet tab) that you want to include. However, to build references that Excel will interpret correctly, we need to concatenate the sheet names to the ranges we need to work with and then use the INDIRECT to get Excel to recognize them correctly.
Also, because the named range "sheets" contains multiple values (i.e. its an array), the result of SUMIF in this case is also an array (sometimes called a "resultant array). So, we use SUMPRODUCT to handle it, since SUMPRODUCT has the ability to handle arrays natively without requiring Ctrl-Shift-Enter, like many other array formulas.
The example above is somewhat complicated. Another way to handle this problem is to do a "local" conditional sum on each sheet, then use a regular 3D sum to add up each value on the summary tab.
To do this, add a SUMIF formula to each sheet sheet that uses a criteria cell on the summary sheet. Then when you change the criteria, all linked SUMIF formulas will update.
In this example the goal is to create a lookup formula with a variable sheet name. In other words, a formula that uses the sheet name in a way that can be changed by referring to up a value on the worksheet. The key to the solution is the INDIRECT...
In this example, the goal is to set up VLOOKUP to retrieve costs based on a variable vendor name. In other words, we want a formula that allows us to switch tables dynamically based on a user-supplied value. There are two cost tables in the...
The syntax for referencing a range of sheets is a built-in feature and works a bit like a reference to a range of cells. For example Sheet1:Sheet3 ! A1 Means: cell A1 from Sheet1 to Sheet3. In the example shown: = SUM ( Week1:Week5 ! D6 ) Will sum...
The Excel SUMIF function returns the sum of cells that meet a single condition. Criteria can be applied to dates, numbers, and text. The SUMIF function supports logical operators (>,,=) and wildcards (*,?) for partial matching....
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.