=MAX(IF((B2:B2327=Q2)*(M2:M2327=R1),H2:H2327))

I left out the *, but still same result.
--
Jules


"Jules" wrote:

Hi Jacob, thank you so much for the help.

=MAX(IF((B2:B2327=Q2),M2:M2327=R1,H2:H2327))

Where r1 is the room number (my room numbers are in R1-w1, 6 rooms)

Returned 0:00:00, I entered it as an array formula. Both dates are in date
format.




--
Jules


"Jacob Skaria" wrote:

With date in D2 and room number in E2 ; try the below formula. Please note
that this is an array formula. Within the cell in edit mode (F2) paste this
formula and press Ctrl+Shift+Enter to apply this formula. If successful in
'Formula Bar' you can notice the curly braces at both ends like "{=<formula}"

Col A and cell D2 should be in excel date format...Try and feedback

=MAX(IF((A2:A10=D2)*(C2:C10=E2),B2:B10))

If this post helps click Yes
---------------
Jacob Skaria


"Jules" wrote:

I have a large data set, column A has many dates (there can be many of the
same dates), B has pt out time, C has room number (1-6).

A
Date pt out room
1/02/09 9:41 1
1/02/09 12:49 2
1/02/09 11:13 1
1/02/09 13:12 3
1/05/09 9:30 2
1/05/09 8:31 4
1/05/09 14:29 4
1/05/09 16:10 4

I need to find the last case of the day in each room.

I tried countif, sumif, it's not working out.

If someone could help me I would be grateful!

Thanks so much,




--
Jules