LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Returning the smallest value above a certain criteria Andrea K Excel Worksheet Functions 4 September 28th 08 06:48 PM
Returning cell value if 2 criteria are met D Zandveld New Users to Excel 4 September 23rd 08 01:02 AM
Maximum value based on criteria Steve Excel Discussion (Misc queries) 1 December 11th 07 03:56 PM
Looking up a maximum value that matches criteria. VLOOKUP? AndyCotgreave Excel Worksheet Functions 4 October 2nd 07 07:36 PM
Minimum and Maximum with Complex Criteria [email protected] Excel Discussion (Misc queries) 4 December 16th 05 06:33 PM


All times are GMT +1. The time now is 08:21 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"