Avoid counting duplicate dates

[bulina2k]

Hello friends,

I'm tring to write a formula to count the working days in a chosen month and in the whole year based on a list with non-working days in that year.
Everything works fine but I have a problem. If someone enters the same date more than once the foumula counts that entry too. How could I modify the formula in E2 and I2 respectively to avoid counting duplicates.

Thank you !

[sandy666]

select your range L18:L42
Data -> Data Validation ->Allow: Custom, Formula: =COUNTIF(L18:L42,L18)>1
ok
try input the same date

but it works only if user type date manually. If he/she paste copied date it will won't work.

[bulina2k]

Thank you sandy!
Your solution goes around my question and prevents the duplicates.
Is there a way for counting the duplicates only once?
Thanks again!

[sandy666]

you mean
05/01/2017
05/01/2017 - counted
05/01/2017 - not counted?

[bulina2k]

If the date is twice or more times in the list it should be counted as 1.
so
01.05.2017 - count (first time in the list)
15.08.2017
01.05.2017 - already in the list - do not count
26.12.2017
01.05.2017 - already in the list - do not count

Here we have 3 distinctive days.

[sandy666]

But this is quite contrary to the topic
but if you wish you can count but not in Data Validation which is not necessary

Count dup only once: =SUMPRODUCT((L2:L26<>"")/COUNTIF(L2:L26,L2:L26&""))

[Phuocam]

Try this ...

D3: =NETWORKDAYS(DATE(B2,C2,1),DATE(B2,C2+1,0))

E3: =D3-F3

F3: =NETWORKDAYS(DATE(B2,C2,1),DATE(B2,C2+1,0),L2:L26)

H3: =NETWORKDAYS(DATE(B2,C2,1),DATE(B2,13,0))

I3: =H3-J3

J3: =NETWORKDAYS(DATE(B2,C2,1),DATE(B2,13,0),L2:L26)

[bulina2k]

thank you Phuocam !
thank you sandy!
Both solutions work great.
I'm gonna mark the topic closed as it hits the goal.
Unfortunately I was expectig a result matrics that have 1's for the first apearance of the date and 0's for the following ones.
But you've been very helpful!
Have fun!

[sandy666]

Glad to help and thanks for the feedback