Lookup formula that omits duplicates

[Drew_]

Hi all.

I have a large data set where I am trying to determine which people worked on specific days. However, there are some instances where multiple people worked on the same day, so I am getting a double count; I only want to count it once. I have 1000 rows of data and I've provided some example data:

John	September		9/13/2017	1
John	September		9/13/2017
John	September		9/14/2017	1

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Paul	September		9/11/2017
Paul	September		9/13/2017	1
Paul	September		9/13/2017
Paul	September		9/13/2017
Paul	September		9/15/2017	1

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

As you can see, a 1 appears each time a person starts a new workday; however, John and Paul both worked on the 13th so my formula is producing a 1 in both cells. I only want a 1 to appear the first time the new date appears (so for Paul on the 13th it would be a blank like the others).

To produce the 1, my formula is very simple:

=IF(D473=D472,"",1) where D has the dates <tbody> </tbody>

<tbody>
</tbody>

I am thinking I need some sort of combination of ROW and OFFSET. Anyone have any ideas? Please let me know if I was not clear enough, thanks for any help

 

[lrobbo314]

Assuming your data is in A1:C8, you could put this in D1 and copy down.

=IF(COUNTIF($D$1:D1,D1)=1,1,"")

Otherwise, if you are just looking for a count of total unique dates, then you could use this formula.

=SUMPRODUCT(1/COUNTIF(D1:D8,D1:D8))

Adjust the ranges to fit your actual data.

 

[Drew_]

This works! Thank you so much!

 

[lrobbo314]