Find MIN value in filtered list

[Mark]

I have a large data set which is filtered based on a
variety of criteria. In each scenario I am attempting to
find the MIN value within a column of the filtered data
set. Unfortunately, the Excel MIN function does not
recognize the effect of the filter. Any suggestions? Thank
you.

 

[Frank Kabel]

Hi Mark
have a look at the SUBTOTAL function. e.g.
SUBTOTAL(105,your_range)

 

[Peo Sjoblom]

Try

=SUBTOTAL(5,Full_Range)

look in help and you will find the different subtotal
MAX is 4 instead of 5, sum is 9...

 

[Frank Kabel]

Hi Peo
but wouldn't that include also the filtered rows?. I would use
=SUBTOTAL(105,Full_Range)

but maybe i'm missing something

 

[Mark]

THANK YOU! That is precisely what I was looking for. Not
very intuitive function...

 

[Peo Sjoblom]

Frank,

I am using a US version of Excel and there is no 105 option there..
It's 1 - 9

 

[Frank Kabel]

Hi Peo
now that is interesting: Using Excel 2003, German version there is this
option. Thhe difference between both is
- Option 105 does not include hidden rows (using 'Format - Row - Hide')
- Option 5 does include hidden rows

Though in this case both options return the same as the OP did not hide
any rows but applies only a filter

 

[Peo Sjoblom]

Actually you are correct, I remember now that excel 2003 has that,
the problem is that it will return a value error on earlier versions.
I only have 2003 installed on one machine at home due to the nasty help in
Office 2003.
I really hate the help function in 2003 so I removed it from all my
computers except one..
But you are correct on 2003 the 1-9 is for all hidden cells either by filter
or format>hide
and the 3 digit ones are for non format>hide