# Solved MIN IF and MAX IF with multiple criteria Excel

Microsoft corporation Excel 2013 32/64-b...
July 29, 2016 at 10:03:06
Specs: Microsoft Excel
 Hi Guys,I have a query here while calculating out the minimum value from a table of entries.Below is the format in which I have my data.Column A : Employee ID(Unique)Column B: Employee NameColumn C: Time StampColumn D : In/OutThere are 17940 entries in this and this data is for the entire week. That means multiple entries in a single day per employee and these type of entries for 5 days of the week in a single excel sheet.Now I want to see that off all the time an employee went In and Out of the office premises, what was his/her First In and Last Out each day in fresh columns.

See More: MIN IF and MAX IF with multiple criteria Excel

July 29, 2016 at 16:57:01
 Taking a shot here...You've got data like this. I'm only using 1 date, but I know you have many more. I'll give you the concept, you work out the rest.``` A B C D 1 ID Name Date In/Out 2 135 Tom 1/1/2016 8:00 3 246 Sue 1/1/2016 9:00 4 579 Mary 1/1/2016 10:00 5 135 Tom 1/1/2016 17:00 6 246 Sue 1/1/2016 18:00 7 579 Mary 1/1/2016 19:00 8 135 Tom 1/1/2016 7:30 9 246 Sue 1/1/2016 10:30 10 579 Mary 1/1/2016 11:30 11 135 Tom 1/1/2016 18:30 12 246 Sue 1/1/2016 19:30 13 579 Mary 1/1/2016 20:30 ```Create a table that contains all names for each date. Again, I'm only using 1 date, you'll have a Name and Date for every date.``` J K L M N 1 ID Name Date Min Max 2 135 Tom 1/1/2016 3 246 Sue 1/1/2016 4 579 Mary 1/1/2016 ```In M2, enter this array formula using Ctrl-Shift-Enter and then drag it down:=MIN(IF(\$B\$2:\$B\$13=K2,IF(\$C\$2:\$C\$13=L2,\$D\$2:\$D\$13)))Ctrl-Shift-Enter will add the {} around the formula.What it should do is find the MIN value of D2:D13 for B2:B13=K2 (Tom) and C2:C13 = L2 (1/1/2016). That should be your earliest In timeRepeat in Column N for MAX.Let us know how that works out for you.message edited by DerbyDad03

#1
July 29, 2016 at 10:19:08
 You do not have a Date Stamp, unless column C is both Date & Time?MIKEhttp://www.skeptic.com/

Report •

#2
July 29, 2016 at 10:26:49
 Ya I have date and time stamp. Sorry I didnt mentioned it properly earlier.

Report •

#3
July 29, 2016 at 15:13:51
 Please post a small representative sample of your spread sheet, but firstread this How-To, which explains the use of the < PRE > tags, to align your dataand please use Column Letters and Row numbers.http://www.computing.net/howtos/sho...MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
July 29, 2016 at 16:57:01
 Taking a shot here...You've got data like this. I'm only using 1 date, but I know you have many more. I'll give you the concept, you work out the rest.``` A B C D 1 ID Name Date In/Out 2 135 Tom 1/1/2016 8:00 3 246 Sue 1/1/2016 9:00 4 579 Mary 1/1/2016 10:00 5 135 Tom 1/1/2016 17:00 6 246 Sue 1/1/2016 18:00 7 579 Mary 1/1/2016 19:00 8 135 Tom 1/1/2016 7:30 9 246 Sue 1/1/2016 10:30 10 579 Mary 1/1/2016 11:30 11 135 Tom 1/1/2016 18:30 12 246 Sue 1/1/2016 19:30 13 579 Mary 1/1/2016 20:30 ```Create a table that contains all names for each date. Again, I'm only using 1 date, you'll have a Name and Date for every date.``` J K L M N 1 ID Name Date Min Max 2 135 Tom 1/1/2016 3 246 Sue 1/1/2016 4 579 Mary 1/1/2016 ```In M2, enter this array formula using Ctrl-Shift-Enter and then drag it down:=MIN(IF(\$B\$2:\$B\$13=K2,IF(\$C\$2:\$C\$13=L2,\$D\$2:\$D\$13)))Ctrl-Shift-Enter will add the {} around the formula.What it should do is find the MIN value of D2:D13 for B2:B13=K2 (Tom) and C2:C13 = L2 (1/1/2016). That should be your earliest In timeRepeat in Column N for MAX.Let us know how that works out for you.message edited by DerbyDad03

Report •

#5
August 1, 2016 at 09:42:03
 Hi Derby/Mike, The data in my spread sheet is a bit different from the way you have shown in last message. However, I align data in my spreadsheet in the same format as you have mentioned and tried using the exact formula you provided. This formula works good for the first row but when I am dragging this further, all it gives is 0. Here is the sample of my spreadsheet.``` A B C D 1 ID Name Stamp In/Out 2 CON-0001 Tom 2016-07-18 11:24:33 In 3 CON-0001 Tom 2016-07-18 11:41:16 Out 4 CON-0001 Tom 2016-07-18 11:52:47 In 5 CON-0001 Tom 2016-07-18 20:21:25 Out 6 CON-0001 Tom 2016-07-19 11:04:51 In 7 CON-0001 Tom 2016-07-19 12:59:54 Out 8 CON-0001 Tom 2016-07-19 18:57:04 In 9 CON-0001 Tom 2016-07-19 19:56:44 Out 10 CON-0002 Mary 2016-07-18 12:30:17 In 11 CON-0002 Mary 2016-07-18 14:21:47 Out 12 CON-0002 Mary 2016-07-18 15:51:32 In 13 CON-0002 Mary 2016-07-18 16:39:01 Out 14 CON-0002 Mary 2016-07-19 12:54:04 In 15 CON-0002 Mary 2016-07-19 16:04:49 In 16 CON-0002 Mary 2016-07-19 18:04:41 Out 17 CON-0002 Mary 2016-07-19 18:19:07 In 18 CON-0002 Mary 2016-07-19 19:38:24 Out 19 CON-0007 Niza 2016-07-18 11:28:10 In 20 CON-0007 Niza 2016-07-18 12:06:21 Out 21 CON-0007 Niza 2016-07-18 13:37:59 Out 22 CON-0007 Niza 2016-07-18 17:46:07 In 23 CON-0007 Niza 2016-07-18 18:23:08 Out 24 CON-0007 Niza 2016-07-19 11:10:02 In 25 CON-0007 Niza 2016-07-19 13:34:43 Out 26 CON-0007 Niza 2016-07-19 18:14:12 In 27 CON-0007 Niza 2016-07-19 18:23:26 Out ```message edited by ArjunBadhan

Report •

#6
August 1, 2016 at 10:05:11
 Your "stamp" combines Date and Time in the same column. That will not work with my suggested formula. The Dates and Times must be in separate columns since the formula is comparing the Times in one column based on a specific Date in another.In other words, it is trying to find the MIN (or MAX) for a given Namefor each unique Date. Therefore, the array formula must see a range of Dates in a separate column.If you separate Column C into C and D, I believe that you should be OK. I tried that and ended up with this:```Tom 7/18/2016 11:24:33 Mary 7/18/2016 12:30:17 Niza 7/18/2016 11:28:10 Tom 7/19/2016 11:04:51 Mary 7/19/2016 12:54:04 Niza 7/19/2016 11:10:02 ```message edited by DerbyDad03

Report •

#7
August 1, 2016 at 13:29:26
 Hi Derby, This worked perfectly. I did it on the entire data and got the desired results. Thanks man for your assistance.

Report •