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
 
Ad

Advertisements

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))
 
Ad

Advertisements

D

D Branca

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

Demetrius
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top