# I'd like to create a MAXIF function

D

#### 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

F

#### 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'

D

#### 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

F

#### Frank Kabel

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

D

#### D Branca

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

Demetrius