Using Excel 3D Formulas on Filtered Lists

By David Hager

Say, for instance, that you have 4 sheets named A-D and on each there is a list with range of A1:E21. If you filter each list and use the following formula:

=SUMPRODUCT(SUBTOTAL(9,INDIRECT(“‘”&CHAR(ROW($97:$100))&”‘!C2:C21”)))

you will get the sum of each list for column C. The best part is that the sheets do not have to be contiguous.

It is important to note that a similar formula does not return a result, although you might expect it to.

=SUMPRODUCT(AGGREGATE(9,6,INDIRECT(“‘”&CHAR(ROW($97:$100))&”‘!C2:C21”)))

EXCEL 3D – Easy as 1-2-3 and A-B-C and Others

EXCEL 3D – Easy as 1-2-3 and A-B-C and Others

By David Hager

A number of years ago (~16) Laurent Longre discovered that Excel formulas that calculated over 3D ranges could be made by using the INDIRECT function(see Issue #3).

https://dhexcel1.wordpress.com/2017/01/07/archive-of-excel-experts-e-letter-by-david-hager/

This formula was developed out of necessity, since many of the functions in Excel do not work with Excel’s built-in 3D cell range notation.

This methodology was refined to formulas of the general form:

=SUMPRODUCT(XXXIF(INDIRECT(“‘”&SOMETHING&”‘!cell_range”),criteria))

In its basic form, it was first developed sometime in the 2004-2005 time period, but it is unclear who was actually the first person to derive the “final” form.

A specific example of this

=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&SOMETHING&”‘!B2:F3”),”<>”&””))

This article is not actually about how to use these 3D formulas. Rather, it is about the formula techniques that can be used to create multi-sheet range in the SOMETHING part of the formula.

In each example shown here, an actual formula/range can be used, or a defined name representing the formula/range can be used.  In the following example, an array created by the ROW function can be used to return sheet tabs named as single digit numbers.

=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&ROW($1:$4)&”‘!B2:F3”),”<>”&””))

Or in the defined name form:

MyNumberTab1 =ROW($1:$4)

=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&MyNumberTab1&”‘!B2:F3”),”<>”&””))

It is important to note that this type of 3D formula has another inherent advantage over Excel’s normal 3D range formulas (i.e. – =SUM($1:$4!B2:F2). The tabs do not have to be contiguous for the formulas to function as intended/desired. If, for example, the sheet containing the 3D formulas was “between” sheet “1” and “3” and had entries in the cell range of that sheet, they would be included in the sum for an Excel-inherent 3D cell reference, but they are not for the INDIRECT 3D reference form.

Another formula returns an array of single letters from a-z.

=CHAR(ROW($97:$122))

So, using this in the INDIRECT formula will return the expected values for the specified formula from all sheets named a-z (26 sheets). Note that all 26 sheets have to exist for the formula to not return an error. Of course, this formula can be modified for an alphabet subset if necessary.

Another formula returns an array of months of the year, which is another popular choice for naming worksheet tabs.

=TEXT(ROW($1:$12)&”-1″,”MMM”)

All of the formula arrays mentioned in this article can be modified by concatenating a text string to the array. In this case, the following array returns 12 strings, starting with “Jan 2011”.

Months2011 =TEXT(ROW($1:$12)&”-1″,”MMM”)&” 2011″

The final example illustrates the simplest form of an array to be used: a simple cell range.

=$A$1:$A$4

Two modifications of this can be useful as well. The OFFSET function could be used to create a dynamic cell range that would expand or contract based on user input.

For cases where the name list is anticipated to be static, the cell range can be evaluated in the formula bar and the resulting array of tab names can be stored as a defined name.

MyTabs1={“John”;”Paul”;”George”;”Ringo”}

I hope that you found this discussion useful.