Identify/Count DISTINCT Names per date

Jennifer Lenander

Employees can provide multiple responses each day if they want. But I only want to count a name once each day. So in the below, employee # 12345 submitted two responses on 12/16. In the 'Count' column, I only want the box checked for one of their submissions on 12/16. Essentially I'm looking for unique employee #s each day. I have tried a couple things and think I'm close but I'm not sure what I'm doing wrong.

=IF(MATCH([Employee #]@row, [Employee #]:[Employee #], 0) = MATCH(Date@row, Date:Date, 0), 1, 0) - this checks the box of the first two boxes but none of the rest.

=IF(MATCH([Employee #]@row, [Employee #]:[Employee #], 0) <> MATCH(Date@row, Date:Date, 0), 1, 0) - this checks the box of the last 5 rows but neither of the first 2.

What am i missing?

Leibel S

Add the below 2 helper columns:

1. "LINE-ID" : Auto Number Column
2. "ROW#" : Column Formula: =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)

You count column should be:

=IF(MIN(COLLECT([ROW#]:[ROW#],[Employee #]:[Employee #],[Employee #]@row,Date:Date,Date@row))=[ROW#]@row,1)

This will check off the first time the employee name shows up on that date.