# Returning a Min and Maximum Value by two criteria

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

J

#### 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

J

#### 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

D

#### 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

J

#### 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

T

#### T. Valko

Don't use SUMPRODUCT:
Don't use an array function:

Don't listen to Bernd! <g>

B

#### Bernd P

Biff volunteers to maintain the user list <bg>

T

I'll do it!