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