Returning a Min and Maximum Value by two criteria

[dapouch]

I need to find the earliest login time and latest logout time by day by user
so that I can work out how long they worked on that day. I have a record of
each login and each logout of the system but can't get my sumproduct to work
("Stolen" from an earlier post in 2007).

The data runs from columns A - D.

The formula I'm trying to use is:

=SUMPRODUCT(--($B$1:$B$35=MIN($B$1:$B$35)),--($D$1:$D$35=$I2),--($A$2:$A$35=J$1))

I2 contains UserA and J1:O1 contains dates

The data is as below

Date Login Logout User
17-Sep 13:05:25 15:45:07 User A
17-Sep 16:27:51 16:35:46 User A
17-Sep 16:37:45 20:02:23 User A
19-Sep 12:04:38 12:24:53 User A
19-Sep 12:25:00 14:20:40 User A
19-Sep 14:22:19 14:41:44 User A
19-Sep 15:20:22 17:00:54 User A
19-Sep 17:07:28 18:00:23 User A
20-Sep 16:06:45 19:59:40 User A

 

[Jacob Skaria]

With USer name in E2 and Date in F2; try the below. Please note that this is
an array formula. You create array formulas in the same way that you create
other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

=MIN(IF((D210=E2)*(A2:A10=F2),B2:B10))
=MAX(IF((D210=E2)*(A2:A10=F2),B2:B10))

If this post helps click Yes

 

[Joel]

Don't use SUMPRODUCT use an array formula

{=MIN(IF(($D$2:$D$10=$I2)*($A$2:$A$10=J$1)*($B$2:$B$10)<>0,($D$2:$D$10=$I2)*($A$2:$A$10=J$1)*($B$2:$B$10),FALSE))}

the curly brackets are the array formula and is produced by
Shift-cntl-enter. don't enter the brackets on the keyboard.

1) Skip header row
2) You have A2 instead of A1 - $A$2:$A$35 You array sizez need to be the
same number of rows
3) Your original formula was only returning 1's or 0's. You had -- in
front of all the tests. the one with the times had tobe a number and you
needed to remove the two dashes
4) The formula produces zeroes for non-mathing users and dates. When yo
have zeroes and you want to find the minimum values excluding zero you need
to have a way of removing the zeroes. I used an IF statement. the formula
was returning zero for dtes the don't match and would do the same for users
that didn't match

 

[dapouch]

Jacob,

Thanks for the quick reply. am I correct in assuming this will only work if
I keep Username in E2 and Date in F2?

Ultimately I would like to fill a table with Dates in Row 1 and Usernames in
column A as below. Ideally I will subtract the min from the max and have the
work time by user by date.

Thanks

Name 17-Sep 18-Sep 19-Sep 20-Sep 21-Sep 22-Sep
User A
User B
User C
User D
User E
User F
User G
User H
User I
User J
User K
User L
User M

 

[Jacob Skaria]

With data in Sheet1; try the below array formula in Sheet2 cell B2

=MAX(IF((Sheet1!$D$2:$D$100=$A2)*(Sheet1!$A$2:$A$100=B$1),Sheet1!$C$2:$C$100))-MIN(IF((Sheet1!$D$2:$D$100=$A2)*(Sheet1!$A$2:$A$100=B$1),Sheet1!$B$2:$B$100))


If this post helps click Yes

 

[Bernd P]

Hello,

Don't use SUMPRODUCT:
http://sulprobil.com/html/sumproduct.html

Don't use an array function:
http://sulprobil.com/html/w-rule.html

Use Mfreq
http://sulprobil.com/html/mfreq.html

Or do you want to maintain a list of users?


Regards,
Bernd

 

[T. Valko]

Don't use SUMPRODUCT:

Don't use an array function:

Don't listen to Bernd! <g>

 

[Bernd P]

Biff volunteers to maintain the user list <bg>

 

[T. Valko]

I'll do it!