Returning a Min and Maximum Value by two criteria

  • Thread starter Thread starter dapouch
  • Start date Start date
D

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
 
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((D2:D10=E2)*(A2:A10=F2),B2:B10))
=MAX(IF((D2:D10=E2)*(A2:A10=F2),B2:B10))

If this post helps click Yes
 
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
 
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
 
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
 
Back
Top