lookup and min time (I think)

[rcsmith]

Hi all,
I am trying (and failing) to find a formula to show me the following:

columnA = names of people
ColumnB = Date
ColumnC = Task number
ColumnD = Time

what I would like to do is a lookup against the name, and in columnE, show the time of the first task that person did for that date.

I can get the overall MIN time, but cannot figure out how to get it to look at the name first.... any thoughts?

thanks,
Rich.

 

[Andrew Poulsom]

Welcome to MrExcel.

Try a formula like:

=MIN(IF(A$2:A$11=A2,IF(B$2:B$11=B2,D$2:D$11)))

which must be confirmed with Ctrl+Shift+Enter, not just Enter. If correctly entered Excel will surround the array formula with curly braces {}.

 

[rcsmith]

That's great, thank you very much.

I now have one more (very small) 'problem' which I didnt realise until just now...

I would like to show only the number of people that started their first task after 9am in a pivot, I can get the count of names where start was after 9am without problem, but it's only the first entry of each person I care about.

Any ideas?

Thanks again,
Rich.

 

[rcsmith]

I think I have it fathomed... A bit of a botch job, but columnF is a countif, and columnG is IF columF is true, count 1. My pivot then is a sum of columnG...

Thanks again for your help.