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

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.

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.

XL3DST1XL3DST2

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)

XL3DST3

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)

XL3DST4

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.

Excel3DSubtotal

 

Advertisements

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

  1. Pingback: #Excel Blog Posts Having Downloadable Workbook Files by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s