Trying to find a max value with criteria

[slot guy]

I want to find the max value within a range of information. I tried to use
the "dmax" function but it will give me the max value of the combined list of
criteria. An example would be like the dmax example that Excel uses in it's
help. I want to get the max value within the range of Apples, Pears,etc.
seperately. What I need to have is a max function that would operate like a
sumif function. Can anyone help me with this one? Excel 2000 user.

 

[JulieD]

Hi

Jason Morin posted this answer to a similar question awhile back:
------
There is no MAXIF function, but you can use an array
formula. For example, return the maximum value in A1:A100
if B1:B100 = "dog":


=MAX(IF(B1:B100="dog",A1:A100)­)


2 important notes:


1. This is an array formula, so you must press
ctrl/shift/enter, not just enter, after inserting the
formula and anytime you edit the cell. XL will place {}
around the formula.


2. Array formulas cannot handle entire columns. You
cannot use:


=MAX(IF(B:B="dog",A:A))