Count unique logs with multiple conditions of multiple sheets

1. 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. May I see a small sample workbook with the expected results?

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

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

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

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

Mon
Col_C Col_D

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

7. Answer is 4

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

Thanks.

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

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

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

11. Examples

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

Robert

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

Users Browsing this Thread

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

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