HELP with Complex formula to derive MAX value in column based on multiple conditions.

[D_Spark]

I have a workbook with 3 column values


Column A is “Site”
Column B is “Date/Time”……format dd/mm/yyyy hh:mm
Column F is “Score”…………..numeric, no decimal places

Total rows 900

I want to create a MAX formula (Column G) that will show, the Max “Score” value, based on the “Site” / “Date” condition.


Eg If

A2= “B”
B2=”12/03/2010 08:00”
F2=”10”
A3=”A”
B3=”12/03/2010 08:01”
F3=”11”
A4=”B”
B4=”13/03/2010 08:14”
F4=”20”
A5=”C”
B5=”11/03/2010 08:14”
F5=”3”
A6=”A”
B6=”11/03/2010 08:59”
F6=”18”
A7=”C”
B7=”11/03/2010 08:16”
F7=”4”
A8=”A”
B8=”11/03/2010 15:59”
F8=”17”


Expected formula results in column G would be

G2=10
G3=11
G4=20
G5=4
G6=18
G7=4
G8=18

 

[Aladin Akyurek]

If you want a formula that you want to copy down:

G2, control+shift+enter, not just enter:

=MAX(IF($A$2:$A$8=A2,IF(INT($B$2:$B$8)=INT(B2),$F$2:$F$8)))

Note. This formula disregards the time component, thinking that it's what you want.

 

[D_Spark]

Thanks Aladin

forumla works great apart from the fact that it there are 2x "Site", "Dates" with the same "Score", then two lines are shown

I only want a max of one line

therefore,can your forumla be adapted so that in the eventuality of 2x "Site", "Dates" with the same "Score" happening then the latest "dd/mm/yyyy hh:mm" wins