# Counting Unique Employees

Options
✭✭✭
edited 01/21/20

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

and so on...

Thanks

Nasir

• ✭✭✭✭✭✭
Options

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.

• Employee
edited 01/22/20
Options

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:

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:

Let me know if this works for you!

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭
Options

Thanks so much Paul!

It worked! :)

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!