Thanks!

I have always considered myself a proficient Excel person, but I have never
known of an array formula!

Can you just give me some insight on how the formula is processed?

It appears that it looks at the variable (lets choose red since it has a
duplicate in my ex.) and then inserts the corresponding cell into the array,
then looks for the next red and inserts that into the array...

So Array = { }
Find Red, lookup 3 -- Array = { 3 }
Find next Red, lookup 4 -- Array = { 3 , 4 }
Find next Red, None -- Evaluate Max{ 3 , 4 }
4.

I'm guessing the Brackets outside the equation changes the Range of A1:A9
into an Array with those corresponding values? (same for column B?)

Sorry, just never seen this and want to clarify how it works.
Thanks!!

"Shane Devenshire" wrote:

Hi

=MAX(IF(A1:A9="x",B1:B9,""))

This is an array formula, so you need to press Shift+Ctrl+Enter to enter it,
not Enter.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Derek P" wrote:

I have a table of data in which I need the max based on which field if next
to the cell. There is Sumif(), Countif() etc...which allows me to complete
the math depending on what the first entry in the row is. I can not find the
Max() equivilent though, and It would be useful in many occasions.

Table Ex.

Yellow....2
Red........3
Red........4
Yellow....7
Blue.......1

On the next page i want the cells to look like (seperated by | )

Yellow | Maxif(max_range, variable_range, Variable)
Blue | Maxif(.....variable is A2 this time...)
Red | Maxif(.......................................)

The closest I could find is DMax but in the constraints section you must
have the column header as part of it, which does not let you search the 2nd
and 3rd entry if you copy down with the references. (my list has over a 100
unique entries...)

If anyone could help that would be appreciated.