Subtotal max/min with IF (urgent help required)

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.

That's not really an option. I generate a report from this data on a regular basis so I need to be able to get this info quickly and easily. Thanks for the suggestion.

Here is an example file demonstrating what I have done and what/why I need it.

Apple-orange-banana filter example.xlsx

This is probably what you need.

Please Login or Register  to view this content.

sample spreadsheet attached

That is perfect! Thank you very very much.

As a matter of refinement, can you think of anyway to make the function ignore zeroes? Maybe a nested IF?

And now I know I'm pushing my luck, can you think of a way to have a second criterion?

Originally Posted by SDCh

Why not use pivot table, you can get all without trouble.

Unfortunately that won't work for my application. I need it to respond quickly and pivots require a refresh.

daddylonglegs: Upon further review I can see that it is actually a problem with the min version of the formula. The max works just fine but the min doesn't. My question about filtering out zeroes is actually redundant because I'm handling that with the autofilter. The problem is that the min function is still finding the zeroes which therefore means that it isn't looking at ONLY the filtered set.

Here's an updated excel sheet:
Apple-orange-banana filter example.xlsx

Daddylonglegs: If I could give you +100 I would. Thank you so much!!