I have a file that has multiple account numbers. I'm trying to get the largest value of the code column (see attached) to align with the account number that has the patient count of "1". Is there a way to use the Max function to do this? Thanks for any help on this.
Conditional MAX Function



Re: Max Function By Account Number
Try this array formula (confirmed with CTRL+Shift+Enter) in N2 and copied down.
[bfn]=IF(L2=1,MAX(($A$2:$A$23=A2)*$K$2:$K$23),0)[/bfn] 
Re: Max Function By Account Number
Hello Keith,
Try this formula in N2 copied down
=IF(L2=1,MAX(IF(A2:A$23=A2,K2:K$23)),"")
It's an array formula and therefore needs to be confirmed with CTRL+SHIFT+ENTER. To do that select cell with formula and press F2. Then hold down CTRL and SHIFT keys and press ENTER so that curly braces like { and } appear around the formula in the formula bar
Note: You probably don't need column L, this would do the same for your example:
=IF(A2<>A1,MAX(IF(A2:A$23=A2,K2:K$23)),"")
also confirmed with CTRL+SHIFT+ENTER

Re: Max Function By Account Number
That works perfectly! Thank you so much for the help...
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!