Min/Max Index Match w/ multiple criteria - Troubleshooting

[JAM7007]

Attached is a basic example version of the file im working on. Basically, the example im going to use is that i need to know how long "Tom" was working on the "2nd FLOOR". I have data on sheet 2 that have rows with columns like Name, Area, and Time. This index formula im working on needs to look at all the "Times" where in that row the name is "Tom" and area is also "2nd Floor", and give me the earliest "Time" in cell C2 in Sheet1 using MIN INDEX MATCH. Then finding the latest "Time" using MAX INDEX MATCH in cell D2 in Sheet1.

I'm having a lot of difficulty and so far have been unsuccesful. I would love to learn this formula so does anybody have any suggestions?

[daffodil11]

=MIN(IF(Sheet2!$A$2:$A$61=Sheet1!$A2,IF(Sheet2!$B$2:$B$61=Sheet1!$B2,Sheet2!$C$2:$C$61)))

=MAX(IF(Sheet2!$A$2:$A$61=Sheet1!$A2,IF(Sheet2!$B$2:$B$61=Sheet1!$B2,Sheet2!$C$2:$C$61)))

Both entered as arrays, using Ctrl+Shift+Enter

Note: The highlighted range on your example does not match the criteria listed on the first tab.

(You have 2nd floor highlighted, criteria on first tab is 1st floor)

[MarvinP]

Hi Jam and welcome to the forum,

This problem looks like a Pivot Table answer to me. Pivot tables allow Minimum and Maximum values to be displayed and they sort and filter. I built a Pivot of your data on sheet2 and displayed the Min and Max times for each person on each floor. Then with a simple subtraction formula and change of display to Time the answer appears. See the attached for what I did.