Oct 8, 2007

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))

View 11 Replies
View Related