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 Name
Column C: Time Stamp
Column D : In/Out
There 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

Report •


✔ Best Answer
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 time

Repeat in Column N for MAX.

Let us know how that works out for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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?

MIKE

http://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 first
read this How-To, which explains the use of the < PRE > tags, to align your data
and please use Column Letters and Row numbers.

http://www.computing.net/howtos/sho...

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
July 29, 2016 at 16:57:01
✔ Best Answer
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 time

Repeat in Column N for MAX.

Let us know how that works out for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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 Name
for 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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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 •


Ask Question