Counting Unique Employees

Nasir@EBC

Hi All

I collect data from the staff in the following form:

|| Date || Employee Name || Task || Units Done || Time Taken ||

I want to write a formula that answers the following question:

How many unique employees worked on "Task X"?

.

I will be formatting the result sheet as:

Task Name -------------- Unique Employee Count

Task A -------------------------------17

Task B -------------------------------19

Task X --------------------------------34

and so on...

Please help!

Thanks

Nasir

Paul Newcome

You could also try something along the lines of...

=COUNT(DISTINCT(COLLECT({Source Sheet Employee Name Column}, {Source Sheet Task Name Column}, [Task Name]@row)))

What this does is collects all of the employee names for the specified task then counts the number of distinct entries.

Genevieve P.

Hi Nasir,

There might be a simpler way of doing this, but the first solution I can think of would be to add two helper columns into the Source Sheet with the form.

The first column would join together your Employee Name and Task Name into one cell, like so:

=JOIN([Employee Name]@row:Task@row, ",")

Then you could have a different checkbox column to see if this combination only happens once in your whole intake sheet. This would use an IF(COUNTIF formula like this:

=IF(COUNTIF([Join Column]:[Join Column], [Join Column]@row) = 1, 1, 0)

Next, in the Results sheet, you can use a different COUNTIFS to count the number of rows that have the specific Task Name you're looking for and the box is checked.

That formula with cross-sheet references might look like this:

=COUNTIFS({Helper Checkbox Column}, 1, {Task Name in source sheet}, = [Task Name]@row)

Here are some Help Center articles that I used to create these formulas:

• JOIN Function / IF Function / COUNTIF Function / COUNTIFS function / Cross Sheet Formulas

Let me know if this works for you!

Cheers,

Genevieve

Paul Newcome

You could also try something along the lines of...

=COUNT(DISTINCT(COLLECT({Source Sheet Employee Name Column}, {Source Sheet Task Name Column}, [Task Name]@row)))

What this does is collects all of the employee names for the specified task then counts the number of distinct entries.

Nasir@EBC

Thanks so much Paul!

It worked! :)

Genevieve P.

@Paul Newcome to the rescue!!! I knew there had to be an easier way.

Paul Newcome

@Nasir@EBC Happy to help! 👍️

@Genevieve P. Haha. I just happened to remember helping with quite a few similar solutions when the DISTINCT function first came out. There were a few of us that did quite a bit of experimenting with it so that we could figure out exactly how it did and did not work, where it went within other functions, etc...

I can't speak for the others, but I for one never completely grew out of the "Can I break it?" and "How far can I push the boundaries?" mentalities from when I was younger. Hahaha.