Exceljet

Quick, clean, and to the point

3D SUMIF for multiple worksheets

Excel formula: 3D SUMIF for multiple worksheets
Generic formula 
=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))
Explanation 

To conditionally sum identical ranges that exist in separate worksheets, all in one formula, you can do so with the SUMIF function + INDIRECT, wrapped in SUMPRODUCT. In the example, the formula looks like this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"D4:D5"),B9,INDIRECT("'"&sheets&"'!"&"E4:E5")))

The data on each of the three sheets being processed looks like this:

Sample data for 3d SUMIFs formula

Explanation

First of all, note that you can't use SUMIFs with a "normal" 3D reference like this:

Sheet1:Sheet3!D4:D5

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.

Another way

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.

Author 
Dave Bruns

Excel Formula Training

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.