ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Returning a Min and Maximum Value by two criteria (https://www.excelbanter.com/excel-discussion-misc-queries/245059-returning-min-maximum-value-two-criteria.html)

dapouch
Returning a Min and Maximum Value by two criteria
 
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
Returning a Min and Maximum Value by two criteria
 
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
---------------
Jacob Skaria


"dapouch" wrote:

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

joel
Returning a Min and Maximum Value by two criteria
 
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" wrote:

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

dapouch
Returning a Min and Maximum Value by two criteria
 
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" wrote:

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
---------------
Jacob Skaria


"dapouch" wrote:

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
Returning a Min and Maximum Value by two criteria
 
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$1 00=B$1),Sheet1!$C$2:$C$100))-MIN(IF((Sheet1!$D$2:$D$100=$A2)*(Sheet1!$A$2:$A$10 0=B$1),Sheet1!$B$2:$B$100))


If this post helps click Yes
---------------
Jacob Skaria


"dapouch" wrote:

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" wrote:

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
---------------
Jacob Skaria


"dapouch" wrote:

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

Bernd P
Returning a Min and Maximum Value by two criteria
 
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
Returning a Min and Maximum Value by two criteria
 
Don't use SUMPRODUCT:
Don't use an array function:

Don't listen to Bernd! <g

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
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



Bernd P
Returning a Min and Maximum Value by two criteria
 
Biff volunteers to maintain the user list <bg

T. Valko
Returning a Min and Maximum Value by two criteria
 
I'll do it!

--
Biff
Microsoft Excel MVP


"Bernd P" wrote in message
...
Biff volunteers to maintain the user list <bg




All times are GMT +1. The time now is 05:01 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com