I have a table which I need to be able to use the autofilter function on but I want to be able to automatically determine the max and min values in the filtered result set which meet certain criteria.

AutoFilter Field Criteria Field Value Field
Orange X 2
Apple X 10
Banana Y 12
Orange X 24
Apple Z 13
Banana Z 14
Banana X 16
Apple Z 18
Orange Y 20

So I need a function that will give me max/min (value field) for the criteria, say X for this example.

I have tried several variations of the SUMPRODUCT function. This is the latest iteration (105 for min, 104 for max):

=SUMPRODUCT(MAX(SUBTOTAL(105,OFFSET(Value_Field,ROW(Value_Field)-ROW(Value_Field),)),--(Criteria Field=B2)))

The problem is that it gives me a the MIN/MAX for the whole filtered set. The criteria is being ignored.