Tom’s Tutorials For Excel: Finding the Minimum and Maximum Numbers in a Filtered List

Tom’s Tutorials For Excel: Finding the Minimum and Maximum Numbers in a Filtered List

You can use the SUBTOTAL function to look up the minimum or maximum number in a filtered list.

In the picture, the formula in cell B1 that returns Sue Flay’s minimum sales number is
=SUBTOTAL(5,B5:B100)

The formula in cell B2 that returns Sue Flay’s maximum sales number is
=SUBTOTAL(4,B5:B100)

The first argument for SUBTOTAL is Function_Num, basically what type of function you’re wanting to perform:
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP