Results 1 to 4 of 4

Thread: Formula help

  1. #1
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I need some help figuring out a formula. I'm thinking it will need to be a sumproduct formula. Basiclly I have two columns of data. One is a user name, the other is a login timestamp. I need two things from this data. The first is the number of logins for each user for a specific time period (weekly). I have this formula already in the attached sample sheet. The formula I need help on is how to count the number of days in that time period where a user actually logged in. I want to be sure this formula can account for a user logging in multiple times in a single day and only counting those multiple logins as a single day. Please see that attached sample sheet and let me know if there are any questions. Thanks for taking the time to read this post.
    Attached Files Attached Files

  2. #2
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry, This is excel 2003 on Windows XP.

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    This might do it.
    I had to create the additional Unique Refs Column in D to make it work
    See Attached

    I added a named Range LoginFor to point at the required Login ID rather than use a literal "abc123"

    [attachment=88535:Example workbook Soln.xls]

    Formula I used was

    =SUMPRODUCT((($B$3:$B$12<>"")*($B$3:$B$12=LoginFor ))/COUNTIF($D$3:$D$12,$D$3:$D$12&"")*($C$3:$C$12>=FoW )*($C$3:$C$12<(CDate+1)))

    A bit of a stinker, but someone else might be able to refine it.
    Attached Files Attached Files
    Andrew

  4. #4
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the suggestion. I'll give it a try.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •