# Using the SUBTOTAL Function in #Excel To Aggregate Values From Multiple Filtered Lists by David Hager

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).

https://dhexcel1.wordpress.com/2014/06/13/using-excel-3d-formulas-on-filtered-lists/

For examples on various types of “multi-worksheet” formulas, see:

https://dhexcel1.wordpress.com/2014/06/09/excel-3d-easy-as-1-2-3-and-a-b-c-and-others/

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.

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

The native Excel method for creating a 3D formula is shown here.

=SUM(A:D!C2:C21)

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

=MIN(A:D!C2:C21)

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

=MAX(A:D!C2:C21)

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!