Count unique values within a range that meet a specific condition

Hi to all,

This is my first post to this forum which has already been a great help for several Excel issues I had to conquer.
But now I am sweating on which formulas to use in order to count unique values within a range that meet a specific condition.

Let's say my range is A1:C6.
The cells in this range are either empty (blank) or contain a date in the format DD.MM.YYYY (defined by Format Cells - Number - Custom), for example 22.05.2013. Dates in this range are non-unique and are not sorted.

Examplev (first 6 rows):
A B C
31.03.2013 13.04.2011
15.02.2012 16.02.2012
22.05.2013
13.04.2011
15.02.2012 16.02.2012
29.01.2010

When I needed to know the count of the unique values in the range A1:C6, I found on this forum the following formula, which works perfectly:
=SUMPRODUCT((A1:C6<>"")/COUNTIF(A1:C6;A1:C6&""))

The next step I need to take is to count the unique values in the range A1:C6 within the same year.
To this purpose I entered in column D, rows 1 - 4: 2010, 2011, 2012 resp. 2013.

My question is: which formula (please no VBA!) should I use in column E which returns the count of unique values in the year indicated on the same row in column D? So D1 should indicate the unique values in the year 2010, D2 in 2011, etc.

Hope I explained myself well, your help is much appreciated, thanks in advance!
CSS

Oeldere, thanks for your quick reply!
Is it also possible to solve my issue by using formulas, so no VBA, no Pivot tables - else re-arranging my data will be my next head ache :-)
BTW, of course the one but last sentence in my original post should have been:
So E1 should indicate the unique values in the year 2010, E2 in 2011, etc.

Thank you SDCh, I used your suggested formula in the xls-file that Oeldere enclosed in his first reply in this thread.
I copied the formula with Ctrl+Shift+Enter there, but the result is: #VALUE!
Could this be because the cells in range A1:C6 are not defined as numbers, but as dates by using Format Cells -> Number -> Custom > dd.mm.yyyy?
Any suggestion to get the formula working with this date format?

SDCH, thanks again for your solution, it works!

Now I am not allowed to change date format as it is defined in my spreadsheet, it has to stay as it is (for definition: see previous my msg).
So the FIND/REPLACE option is a "no go".
Is there a way to automatically "convert" a date in my format to a date in another column with your format that will make the formula work?
I tried several ways, such as concatenating, so a date 29.01.2010 is transformed to 29-1-2010 in the next column, where it is defined as a date according to your format, but the formula doesn't work :-(

Sorry for not expressing myself clearly enough. That’s why I decided to put my situation in the file which is (hopefully) uploaded with this message.
I used the columns H – S in the first sheet.

Column H (green background) is the only column available for the user of my spreadsheet to enter the date.
However, the date can also be two days, see entered examples.

The other columns I – Q are hidden for the user and I use them to get the relevant dates separately in columns J – L (blue background).

Orange background:
column R shows the years for which the number of unique dates have to be counted
column Q contains the =SUM(--(FREQUENCY(IF(YEAR($J$1:$L$6)=R1;$J$1:$L$6);($J$1:$L$6))>0)) formula entered with Ctrl+Shift+Enter which results in #VALUE!.

Where do I go wrong?

Please note the corrected column reference below.
------
Sorry for not expressing myself clearly enough. That’s why I decided to put my situation in the file which is (hopefully) uploaded with this message.
I used the columns H – S in the first sheet.

Column H (green background) is the only column available for the user of my spreadsheet to enter the date.
However, the date can also be two days, see entered examples.

The other columns I – S are hidden for the user and I use them to get the relevant dates separately in columns J – L (blue background).

Orange background:
column R shows the years for which the number of unique dates have to be counted
column S contains the =SUM(--(FREQUENCY(IF(YEAR($J$1:$L$6)=R1;$J$1:$L$6);($J$1:$L$6))>0)) formula entered with Ctrl+Shift+Enter which results in #VALUE!.

Where do I go wrong?

Hi benishiryo,

Thanks for your reply and the provided formula.

You apply your formula to my column H, which can also contain a range of two days as I already explained.
Therefore your formula does not give correct results, see enclosed file in which I added your formula in column T.
The count of unique dates in 2011 is 6, your formula gives 5.
The count of unique dates in 2012 is 2, your formula gives 1.

In order to get all dates separately, I used columns J, K and L.
How do I get your formula working to get the unique dates for the years 2010 – 2015 whilst using columns J, K and L (instead of column H)?
Thanks in advance for enlightening me!