I'd like to create a MAXIF function

[dbranca]

I'd like to create a function for Excel 02 that would perform similarly to
the SUMIF function. However I need it to find the the MAX, instead of
calculating the SUM. The closest worksheet function I can find is the
DMAX, but this does not quite fulfill my needs.
Thanks,
Demetrius

 

[Frank Kabel]

Hi
if your lookup values ares tored in column a and the values in column B
try
=SUMPRODUCT(MAX((A1:A100="value")*(B1:B100)))

this will give you the maximum in column B for all rows in which column
A equals 'value'

 

[dbranca]

Thank you, it worked perfectly!
Can you show me how to do something similar for a MINIF situation. I
tried to make changes to the function you showed me but had no luck.
Thank you!
Demetrius

 

[Frank Kabel]

Hi
try the array formula (entered with CTRL+SHIFT+ENTER):
=MIN(IF(A1:A100=value,B1:B100))

 

[D Branca]

Vielen Dank Herr Kabel,
Gruesse aus Tallahassee Florida, von einem ehemahligen Mannheimer!

Demetrius