find maximum and minimum based on multiple criteria

[jb]

Hi all experts,
I have an excel sheet with 4 columns: student type, gender, area and marks.
this sheet contains multiple records in random order.
student type can be G / P / T etc.
gender is M / F
area is U / R / L etc.
And 4th column is marks.
Now I want to find out maximum and minimum marks based on different criteria combination.
For e.g. Find out maximum mark from all records whose student type = G and gender= F and area = U.

I am attaching sample file.
Formula is required for this. Please do not give me macro.

 

[pecoflyer]

Something like =SUMPRODUCT(MAX((A2:A18="g")*(B2:B18="f")*(C2:C18="r")*(D2:D18))) ?

 

[jb]

thanks pecoflyer. thanks a lot. it worked.