3D SUMIF for multiple worksheets

3D SUMIF for multiple worksheets

Generic formula 

=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))

Related formulas 

Lookup with variable sheet name

Dynamic lookup table with INDIRECT

3D sum multiple worksheets

Explanation

If you need 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:

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.

3D SUMIF for multiple worksheets

Generic formula 

=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"rng"),criteria,INDIRECT("'"&sheets&"'!"&"sumrng")))

Related formulas 

Lookup with variable sheet name

Dynamic lookup table with INDIRECT

3D sum multiple worksheets

Explanation

If you need 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:

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.