How do I not include duplicate values in my function?

**uncover123abc** · 11-09-2016, 02:26 PM

I have been given a set of data that is made up of dates ranging from 1/1/2013-12/31/2015. I have been asked to determine how many days each year Employee #2 has made a sale (i.e. how many days in 2013, 2014, 2015)

For example:

Employee two has sales recorded on these dates:
Column E:

1/1/2013
1/1/2013
1/1/2013
1/2/2013
1/5/2013
1/6/2013
1/6/2013
1/10/2013

Right now, for 2013, I have:

=COUNTIFS(E25:E8095, ">=1/1/2013", E25:E8095, "<=12/31/2013"

However, I do not want it to count 1/1/2013 three times, I just want it to record it once. How do eliminate duplicate values in my equation?

Thank you!

**Pete_UK** · 11-09-2016, 02:39 PM

You could use column F (or some other empty column) as a helper to filter out those duplicate values, using a formula like this in F25:

=IF(COUNTIF(E$25:E$8095,E25)=1,YEAR(E25),"")

then copy that down to F8095. Then your counting formula would become:

=COUNTIF(F$25:F$8095,2013)

and similar for the other years (just change the 2013).

Hope this helps.

Pete

**eibi** · 11-09-2016, 02:42 PM

**** revised -- my first suggestion didn't work ****

This will give you a total of unique values, but not by year:

=SUM(--(FREQUENCY($E$25:$E$8095,$E$25:$E$8095)<>0))

To get it to test by a specific year, you can wrap it in a SUMPRODUCT():

=SUMPRODUCT((FREQUENCY($E$25:$E$8095,$E$25:$E$8095)<>0)*(YEAR($E$25:$E$8096)=2013))

Note that the segment of the formula in bold actually extends 1 row further than the range of dates. That's intentional; don't let it trip you. (see sample, attached)