Using IF, AND and MAX in one formulae

[Steve E]

I am trying to create a formula that does the following:

Lookup a range from 3 separate columns. If the criteria are met, then I want Excel to pick the max value from another column range and place the result in another cell.

Is this possible in one complete formula?

I am currently using something like this, which returns the MAX value for all the data, rather than the criteria:

=IF(AND(C6:C199="SO",AND(E6:E199="S",F6:F199="East"),MAX(AG6:AG199)).

Using Excel 2000.

Thanks, Steve.

 

[Steve E]

This now appears to be working fine. Have re-checked the data manually and came up with the same figures.

Thanks for all your help on these!

Regards, Steve E.

 

[T. Valko]

This now appears to be working fine. Have re-checked the data manually and came up with the same figures.

Thanks for all your help on these!

Regards, Steve E.

You're welcome. Thanks for the feedback!