So, imagine a scenario where 4 filtered lists reside in the same position on adjacent worksheets that have “sequential” names. Perhaps each list comes from a monthly summary, but filtering is applied differently for each list. Well, I did not build that example, but instead have 4 sheets named A to D, a list on each sheet residing in the same range and each column filled with numbers from 0 to 99. The object is to perform aggregations on the filtered data from all sheets at once (or, creating a 3D formula).
Actually, I have already written a blog post about this subject, but I felt that it would be useful to cover this again in an expanded version and provide a workbook to download.
For examples on various types of “multi-worksheet” formulas, see:
The figures below shows the results of the 2 types of 3D formulas.
The filter-enabled formula shown below sums the filtered values in the 4 filtered lists. Note that since the SUMPRODUCT function is used here, the result is NOT an array formula.
The native Excel method for creating a 3D formula is shown here.
The result from this formula is a number much higher than the filter 3D formula since there is no way to use the native 3D referencing to construct a filtered result.
Other types of aggregation from the filtered 3D formulas, but they must be constructed as array formulas. The following formulas show the filtered MIN for the desired range and the corresponding Excel-centric 3D formula.
=MIN(SUBTOTAL(5,INDIRECT(“‘”&CHAR(ROW($97:$100))&”‘!C2:C21”))) ‘array formula
This example shows the formulas for the MAX 3D-filtered and Excel 3D.
=MAX(SUBTOTAL(4,INDIRECT(“‘”&CHAR(ROW($97:$100))&”‘!C2:C21”)) ‘array formula
There are obviously a number of variations on this theme possible. Knowing that this type of aggregation is available could possibly change the way you analyze your data. HTH!
You can download the file here.