+ Reply to Thread
Results 1 to 12 of 12

Count unique logs with multiple conditions of multiple sheets

  1. #1
    Registered User
    Join Date
    10-08-2007
    Posts
    8

    Count unique logs with multiple conditions of multiple sheets

    Hello. I've got no clue about all this, but I've had to get specific formula examples and fill in the blanks in order for my timesheet to work. There's just one final problem if somebody could please help.

    This is a timesheet for a 5 day work week. I need to count the number of unique log numbers for a specific activity. The log numbers counted must be unique across the entire week, not just for each day, which means I want the formula to count the unique log numbers across multiple sheets.

    The formula also has multiple conditions. I got 2 columns. The first part of the formula needs to verify a word, say, "split" and if it does it checks the adjacent cell for a unique log number. If both arguments are true, it counts the log as 1 unit.

    Here is a working formula for only one page.
    =COUNT(IF(D4:D29="split",IF(FREQUENCY(C4:C28,C4:C28)>0,1,)))

    Here's 2 problems with this formula:
    1. I will count if it encounters a blank cell in the Log numbers the first time (which will happen as not every activity we do has a log#), but it will stop counting if it encounters a second blank cell.

    2. I don't know how to make it work across several sheets.

    This is an alternate formula which works and skips the blank cells, but I don't know how to add the multiple condition of "split" and to have it work across multiple sheets. I just copied it Microsoft. As I said, I don't understand it, I just fill in the blanks.

    SUM(IF(FREQUENCY(IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""), IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""))>0,1))

    Any help is appreciated. Thanks.

    Rob

  2. #2
    Forum Contributor
    Join Date
    09-25-2004
    Posts
    269
    May I see a small sample workbook with the expected results?

  3. #3
    Registered User
    Join Date
    10-08-2007
    Posts
    8
    timesheet page.JPG

    timesheet endweek tallyl.JPG

    Appreciate your help, Vane.

    Here's some visual samples. I was trying to make it work on the Timesheet Page and then adapt it to work for all the sheets on the Timesheet Tally (since I need to really count all the "unique" logs for the entire week, not for each page, or day).

    Hope the samples are what you're looking for. Thanks.

    Rob

  4. #4
    Forum Contributor
    Join Date
    09-25-2004
    Posts
    269
    I will need you to upload a excel file so I could better come up with a solution. Here is a link that you could winzip and upload your file here on this forum.

    http://www.download.com/3000-2250-10003164.html

  5. #5
    Registered User
    Join Date
    10-08-2007
    Posts
    8

    Timesheet excel

    Vane:
    Here's the timesheet. The main formulas are on the bottom of the page. If anything is protected that I haven't unprotected, you should able to change it since there's no password.

    Thanks again for taking the time. I hope this isn't that difficult. As I said, the formulas I have work except for a couple of bugs as mentioned, such as being stumped by blank cells and not knowing if the formula will work over a series of sheets. Appreciate your time.

    Rob
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    09-25-2004
    Posts
    269
    Hi Robert Bob


    Thanks for posting the example. Before I give a solution I would like to make sure what you are looking for. You want to count uniques across per worksheets or the entire workbook?

    Example of the 2 worksheets below:

    Fri
    Col_C Col_D

    1234567 Add to
    7897898 Split
    Other
    9879878 Split
    Other
    7896574 Split
    9877895 Consol
    1324567 Consol
    6478958 Split
    4659877 Issue
    7896457 Issue


    Mon
    Col_C Col_D

    1234567 Add to
    7897898 Split
    Other
    9879878 Split
    Other
    7896574 Split
    9877895 Consol
    1324567 Consol
    6478958 Split
    4659877 Issue
    7896457 Issue


    Is the answer is 8 Per worksheet

    or

    Is the answer is 4 Entire Workbook
    Last edited by vane0326; 10-09-2007 at 10:57 PM.

  7. #7
    Registered User
    Join Date
    10-08-2007
    Posts
    8

    Answer is 4

    Vane, the answer is 4, so I'm looking to count unique log numbers across the entire workbook.

    Thanks.

  8. #8
    Forum Contributor
    Join Date
    09-25-2004
    Posts
    269
    Let me know if this is correct. Look at worksheet "Total" in cell C7.

    I use some Define name range formulas to get the result.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    10-08-2007
    Posts
    8

    Thanks for the help but..

    Vane, thanks for the help I appreciate it. Sorry it took me a while to write back. That timesheet I gave you was a working copy so I've been trying to transfer the formula and such to the updated timesheet (version 1.2) but it's not working, since it's dealing with ranges and such.

    I'm wondering if you would be able to tell me how you did it so I can transfer it the new page (which I will have to update also in a couple more days). I have the new timesheet if it would be easier for you to just transfer it than explain it all. Even a basic lowdown would be grand and I could look into the help file to figure the rest out.

    Thanks a lot for your help. I appreciate your time. I've spent hours and hours trying to figure this out.

    Robert
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    09-25-2004
    Posts
    269
    Here is one example to input the formulas in the Define name range box.


    First list your worksheets tabs names in cells A2 thru A6.

    Now,

    1.) Go to the menu bar at the top of your excel and Choose Insert | Name | Define . In the first box type:

    WSLST


    2.) Now In the Refers To box box select your range A2:A6 and then Click the Add button.

    You'll probably be thinking why not just create a monster formula and input in the cell. Well it won't work I tried and some reason excel won't allow it.


    Here is a link you could read up on.

    http://www.bettersolutions.com/excel...L325782331.htm


    Also I left the Attachment below.
    Attached Files Attached Files
    Last edited by vane0326; 10-11-2007 at 10:48 PM.

  11. #11
    Registered User
    Join Date
    10-08-2007
    Posts
    8

    Examples

    That's grand, Vane. I'll look into this and let you know how things work out. Thanks again.

    Robert

  12. #12
    Registered User
    Join Date
    10-08-2007
    Posts
    8

    Works Grand

    Vane:
    Just wanted to let you know that I got all that working for all the different types of logs (Split, consol, issue, etc.). That was the final piece to the timesheet puzzle. This will save me a lot of time as trying to count the unique logs was the most time-consuming part of the sheet.

    Thanks again for all your help, time and effort.

    Robert

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1