We help IT Professionals succeed at work.

Using MAX function with nested AND statement within an IF statement

Hobart007
Hobart007 asked
on
Medium Priority
231 Views
Last Modified: 2012-05-11
I am trying to write a formula in my Excel spreadsheet to perfrom the following:

For a value in column A on Sheet 1, I am trying to look up the corresponding value in a named range (column A on sheet 2) named 'NAME' and return the highest value from named range (column D on sheet 2) named 'DATE' where the parameter in named range (column B on sheet 2) named 'TYPE' is equal to ACTIVE.

Here is what I have so far:
{=MAX(IF(NAME=A2,DATE,0))}

This returns the highest value fromt he DATE range regardless of type.

The change I tried to make is as follows:

{=MAX(IF(AND(NAME=A2,TYPE="ACTIVE"),DATE,0))}

This returns a value of zero rather than the highest value int he DATE range where NAME = A2 and TYPE = ACTIVE.

Can someone help me with what I am doing wrong?
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2008
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2008
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you both for your answers.  The formula worked.  My issue now is that when I add the formula to a few hundred cells it crashes my pc every time I update something or try to filter my pivot table.

Is there a less system intensive way to do this?
CERTIFIED EXPERT
Top Expert 2008

Commented:
Array formulas are more CPU intensive than other formulas, but a few hundred should not affect the performance of your system. And they should most definitely not cause Excel or Windows to crash. Might be time to rebuild.

Kevin
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.