Excel 2007 - Combination subtotal/sumproduct formula 1

[BxWill]

Have a 10 column worksheet with autofilter on each column with data beginning in row 15.

Column G for Rows 4 through 7 contain the following subtotal formulae to display Count, Avg, Min, and Max, respectively;

=SUBTOTAL(2,G16:G562)
=SUBTOTAL(101,G16:G562)
=SUBTOTAL(5,G16:G562)
=SUBTOTAL(4,G16:G562)

Is it possible to modify the formulae above so that I can display the Count, Avg, Min, and Max only for those cells in column G that have a value greater than 100 when I filter on columns A through F?

Plan to eventually copy the formulae across the columns to column M if all goes well...

Currently experimenting with a combination sumproduct/subtotal approach but need some insight...

 

[GlennUK]

Can I ask why you can't just also filter column G to be >100?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[BxWill]

Would like to display the metrics for the records less than or equal to 100 and the records greater than 100 at the same time.

 

[SkipVought]

Do you have subtotal values within the aggregation ranges?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}