max if formula

Can you give some more explanation?

please check the excel file.. i tried this formula to get latest production date and time
=MAX(IF($D$17:$D$30=D31,$J$17:$J$30,$C$17:$C$30))
please check file and help to get the solution

Can you give us your expected result

if i entered "D" in the d32 cell the 6:00A.M should appear in the result cell..

First, you need to lock the ranges in your formula

You have this in K31
=MAX(IF($D$17:$D$30=D31,J17:J30))

As you fill it down to K32, it changes to
=MAX(IF($D$17:$D$30=D32,J18:J31))
the D31 is supposed to change to D32, but the J17:J30 is supposed to stay the same.
So the formula in K32 is pulling the result from the wrong place (1 cell down)

So the formula should be
=MAX(IF($D$17:$D$30=D31,J$17:J$30))

Now, why do you expect the result should be 6AM ?
the MAX (largest) time value in column J where Column D = "D" is NOT 6AM, it's 11:30PM in Cell J21

Maybe 11:00 AM?

Try lock range:

Formula:

Please Login or Register  to view this content.

here the time columns has been taken only..i would like to take both date and time.
so i expected the result 6:00 AM
=MAX(IF($D$17:$D$30=D31,J$17:J$30,C$17:C$30))
but i did not the result..

Try this in K31 and filled down.

=MAX(IF($D$17:$D$30=D31,$C$17:$C$30+$J$17:$J$30))

OK. This is formula you need to use, only you need to add date in 2-Jun-13 in C32 (in front of D):

Formula:

Please Login or Register  to view this content.

=MAX(IF($D$17:$D$30=D31,$C$17:$C$30+$J$17:$J$30))
This formula works well as i expected..
This you very much Mr. Jonmo1 and Mr.zbor
thank a lot..

This works as CSE formula only..
=MAX(IF($D$17:$D$30=D32,$C$17:$C$30+$J$17:$J$30))
if i try to drag the formula it did not work correctly.
i need to edit (CSE) every cell.. is there any way with out CSE..

CSE should work when you dragging formula down.

Thank you very much Mr.Fotis..