# Thread: Formula help

1. 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.

2. Sorry, This is excel 2003 on Windows XP.

3. 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.

4. 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
•