Counting only unique values

enter this array / CSE formula in 'Sheet1 (2)' cell L2.

=SUM(IF(FREQUENCY(IF('Violations 2'!$A$2:$A$434>='Sheet2 (2)'!$I$2,IF('Violations 2'!$A$2:$A$434<='Sheet2 (2)'!$I$3,IF('Violations 2'!$D$2:$D$434=--RIGHT('Sheet1 (2)'!L$1),MATCH('Violations 2'!$C$2:$C$434,'Violations 2'!$C$2:$C$434,0)))),(ROW($A$2:$A$434)-MIN(ROW($A$2:$A$434))+1))>0,1))

then, you can drag it right.

i got the result of 3 for dates in the range 23-06-10 and 28-07-10, for Level 1. by the way, what is X1? i could not find any corresponding column in your database, so i just left it alone.

in case you just want to find the unique count for a specific date, you would simplify the formula thus:

=SUM(IF(FREQUENCY(IF('Violations 2'!$A$2:$A$434>='Sheet1 (2)'!$K$1,IF('Violations 2'!$D$2:$D$434=--RIGHT('Sheet1 (2)'!L$1),MATCH('Violations 2'!$C$2:$C$434,'Violations 2'!$C$2:$C$434,0))),(ROW($A$2:$A$434)-MIN(ROW($A$2:$A$434))+1))>0,1))

EDIT:

the above formula had an error. i have fixed it; see the attached file. in order for the formula to be as automated as possible, the table header on Sheet2 (2) had to be changed to the one highlighted in Yellow and Red, with right-most values being X1, X2 and X3. sans that, the formula would become very cumbersome.

also, keep in mind that you have to explain your needs well. as you can see, in this case, it took a couple of rounds for you to explain your requirements well. this sort of back and forth is a waste of time for both parties.