Combining MAX with IF

Hi,

In column (A) a list of City's - same city will appear many times.

In column (B) a date relating to the city.

Want to have a formula that gives me the newest date for one specific city.

Newest date is of course highest number , so that leads me to MAX formula - but i need to combine it with an IF to get some "MAXIF" formula ???

Any hint or suggestions?

You can use an "array formula" like this

=MAX(IF(A2:A100="Bergen";B2:B100))

confirmed with CTRL+SHIFT+ENTER

You can use an arrayed formula (enter with CNTRL SHFT ENTER rather than ENTER. If done properly you'll see brackets {} around your formula)

with City in A and Dates in B (and City you want to find in D1)

=MAX(IF(A2:A6=D1,B2:B6,""))
Does that work for you?

Let's say the list is in cells A2 to A13 (a, b and c) and the dates are in B2 to B13

Then: =MAX(IF($A2:$A13="a",$B2:$B13)) ... Array Entered with Ctrl-Shift-Enter will give you the highest value/date for "a"

To generalise:

=MAX(IF($A$2:$A$13=C2,$B$2:$B$13)) ... again, Array Entered with Ctrl-Shift-Enter

HTML Code: 

Regards, TMS

You're welcome. Thanks for the rep.