1

I have an excel sheet with productivity for my work force. Along with lots of other information about the job, it contains a job number, date, and completed by fields. Each employee may have 50-60 entries for each day they worked.

What I would like to be able to do:
Count how many days each employee has worked by applying a formula that counts a day as work if the employee has any entry against that date (this tracker if for a full year so I'm looking for a way that avoids typing each day of the year).

For example, I want to be able to say to employee A, "You completed 4,000 jobs over 165 days which puts you productivity rate at..."

I want to count the unique values in the date column if Name = Employee A. My data set is about 20K records covering a full year.

[This is the data i have, with 20k rows1

This is how i wish to present

JasonAizkalns
  • 20,243
  • 8
  • 57
  • 116
custhasno
  • 25
  • 4
  • [HERE](http://stackoverflow.com/questions/15944249/count-unique-values-with-a-condition) is a good starting point. Try these suggestions if you get stuck come back with a more specific question and mocked up data so we can be more specific in the answer. – Scott Craner Mar 14 '16 at 15:29
  • 1
    Welcome to Stack Overflow. Please have a look at the [help center](http://stackoverflow.com/help/on-topic) specifically: _3. Questions asking for homework help must include a summary of the work you've done so far to solve the problem, and a description of the difficulty you are having solving it._ – Chrismas007 Mar 14 '16 at 15:30
  • how do i insert a screen shot? – custhasno Mar 14 '16 at 15:38
  • You link to an outside source like imugr or dropbox. – Scott Craner Mar 14 '16 at 15:38
  • 1
    But we would prefer a mock up in the OP, so we do not need to type examples to test. – Scott Craner Mar 14 '16 at 15:39
  • 1
    Put it in the Original post not in a comment. – Scott Craner Mar 14 '16 at 15:44

1 Answers1

2

How about something like this:

Excel Table

Where Unique Jobs and Unique Dates are calculated with:

{=SUM(--(FREQUENCY(IF($A$2:$A$13=A2,$B$2:$B$13),IF($A$2:$A$13=A2, $B$2:$B$13))>0))} and {=SUM(--(FREQUENCY(IF($A$2:$A$13=A2,$C$2:$C$13),IF($A$2:$A$13=A2, $C$2:$C$13))>0))} respectively. Note the formulas are array formulas which means they need to be entered with CTRL + SHIFT + ENTER.

The IF statement handles the "are you the employee I care about?" aspect and returns an array of jobs or dates. We take advantage of the fact that when we bin with the same array with FREQUENCY, we return a zero for any value it has already seen. The double negation, --, coerces the numbers to booleans to ones-and-zeros.

More explanation is available here.

It's likely possible to do this without the FREQUENCY approach or without array formulas, but depending on the size of the dataset, you might want to consider the performance hit.

UPDATE (per comments):

If you have an additional column, say column D = Job_Type, you can add criteria to the IF condition, like so:

{=SUM(--(FREQUENCY(IF(($A$2:$A$13=A2)*($D$2:$D$13="Job Type X"),$B$2:$B$13),IF(($A$2:$A$13=A2)*($D$2:$D$13="Job Type X"),$B$2:$B$13))>0))}

By utilizing multiplication with an asterisk, *, we are implicitly implying an AND condition in the logic; note, if we wanted OR logic we could use +. Also note the additional parentheses. At the end of the day, this says, first check if this is the Emp that I care about; next, check if this is the Job_Type that I care about; if both of those checkout, return me the column of interest and continue as above.

NOTE: The method just described will make things yield the same results, by employee, for the specified Job Type -- in this example, records with Job_Type == "Job Type X". You could do something like: {=SUM(--(FREQUENCY(IF(($A$2:$A$13=A2)*($D$2:$D$13=D2),$B$2:$B$13),IF(($A$2:$A$13 = A2)*($D$2:$D$13=D2),$B$2:$B$13))>0))} and you would obtain different productivity amounts for each Emp and Job_Type combination.

Alternatively, you could create a new Key column, such as Emp_Job_Type which would concatenate the two columns, something like: =A2&D2.

Finally, if you are only interested in one particular Job_type, you could also consider subsetting or filtering your data beforehand. That is, filter for the records of interest upfront and create a new dataset.

Community
  • 1
  • 1
JasonAizkalns
  • 20,243
  • 8
  • 57
  • 116
  • Depends on how you want to count them. Is the combination of a worker and the job type important? If that's the case, create a new column, that's called `Emp_Job_Type` which `=Emp&Job_Type` (concatenate the two fields together) and treat it as if this was the `Emp` column in my example. – JasonAizkalns Mar 14 '16 at 16:50
  • i want to count the total number of unique days that Emp A does any "All Day jobs", your formula above gets me to the count of every job. Can this bee added without an extra column as an extra criteria for the IF? – custhasno Mar 14 '16 at 16:55