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

Advertisements

2 thoughts on “Using Excel 3D Formulas on Filtered Lists

  1. Pingback: Using the SUBTOTAL Function in #Excel To Aggregate Values From Multiple Filtered Lists 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