How to use MAX function with multiple condition

**MAHMUZ** · 08-05-2010, 05:04 AM

Hi;
I need your help to extract the maximum record based on DATE & CONTACT & ACTION. The attached file showing the correct result but it done "manual ", if there is some records are having the same DATE then the maximum record will be whoever having the flag of "CONP" in column ACTION.

I am using EXCEL 2007

BR

**zbor** · 08-05-2010, 05:52 AM

This will give you TRUE or FALSE regarding to contact but doesn't consider CONP criteria:

=MAX(IF($A$2:$A$42=A2,$C$2:$C$42))=C2

comfirmed with ctrl+shift+enter

**zbor** · 08-05-2010, 06:00 AM

This should do your quest:

=MAX(IF($A$2:$A$42=A2,$C$2:$C$42-CODE(LEFT($B$2:$B$42,1))))=C2-CODE(LEFT($B2,1))

comfirmed with ctrl+shift+enter

Edit: note that criteria must be sorted alphabeticaly: CONP > DINP > INST > WARN

**DonkeyOte** · 08-05-2010, 06:15 AM

Using your original sample file wherein we assume sorting data is not permitted (else we would sort by date etc) then:

D2:
=REPT("P",(MAX(IF($A$2:$A$42=$A2,$C$2:$C$42))=$C2)*OR($B2="CONP",ISNA(MATCH($A2&"@CONP@"&$C2,$A$2:$A$42&"@"&$B$2:$B$42&"@"&$C$2:$C$42,0))))
confirmed with CTRL + SHIFT + ENTER (enter alone will not suffice)
copied down

it's not clear what should happen in scenarios where multiple MAX dates are located for a given contact but none of those dates has COMP action - ie which is then to be selected ?

the above is merely an alternative... a little long winded I grant you !

**zbor** · 08-05-2010, 06:38 AM

now I see that CODE might enter sometimes error between dates so dividing with some number would be better:

=IF(MAX(IF($A$2:$A$42=A2;$C$2:$C$42-CODE(LEFT($B$2:$B$42;1))/1000))=C2-CODE(LEFT($B2;1))/1000;"P";"")

**MAHMUZ** · 08-08-2010, 01:32 AM

Thanks for all your feedback, it really helpful and useful.

**nasg** · 10-25-2010, 03:22 AM

Hi there;
can anyone help me to find the max of following series..Please note that I have more names and numbers in my hand therefore I wont be able to use individual search, it should be generic.

John 27.89
John 28.26
John 28.31
John 26.3
John 24.69
John 22.79
John 30.01
Cate 11.93
Cate 11.92
Cate 11.28
Cate 10.63
Cate 9.74
Cate 8.45
Cate 10.46
Joe 13.39
Joe 12.47
Joe 13.09
Joe 14.55
Joe 13.19
Joe 14.24
Joe 14.02

results must be only in following format for above list.

John 30.01
Cate 11.93
Joe 14.55

Many thanks in advance.

**arthurbr** · 10-25-2010, 03:30 AM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.