Tip Printed from ExcelTip.com

---

Retrieving the Minimal / Maximal Values from a Filtered List


Problem:

An auto filter is set for List1 (column A) and List2 (column B).
When setting the filter of List1 to "2", the number "5" is the minimal value displayed in List2.
However, when using the MIN function, a minimal value of "2" is returned, which is not among the filtered data.
We want to create a formula to find the true minimal value within the filtered data, one that updates with every change of filter.

Solution:

To retrieve the minimal value use the SUBTOTAL function as shown in the following formula:
=SUBTOTAL(5,B13:B21)

To retrieve the maximal value use the SUBTOTAL function as shown in the following formula:
=SUBTOTAL(4,B13:B21)