Count unique dates in a column

[stepan1987]

Guys,
Please advise a formula with which I can count the number of unique dates in a column? I bet there is one..

Thanks!

 

[Sektor]

@if(countif(a:a;"17.05.2011")=1;1;0)

 

[kidwispa]

Assuming you want to count in column A...

=sumproduct(1/countif(A:A,A:A))

 

[Aladin Akyurek]

Guys,
Please advise a formula with which I can count the number of unique dates in a column? I bet there is one..

Thanks!

Control+shift+enter, not just enter...

=SUM(IF(FREQUENCY(IF(A1:A5<>"",MATCH("~"&A1:A5,A1:A5&"",0)),ROW(A1:A5)-ROW(A1)+1),1))

Since you have just dates, the following would also suffice:

Control+shift+enter, not just enter...

=SUM(IF(FREQUENCY(IF(A1:A5<>"",MATCH(A1:A5,A1:A5,0)),ROW(A1:A5)-ROW(A1)+1),1))

Adjust the range to suit.

 

[stepan1987]

Thanks,
But first one does not suit as I will need the formula work after 17.05 as well..
And the second one somewhy makes my excel hang for 2 minutes))
any other suggestions?

 

[Aladin Akyurek]

Thanks,
But first one does not suit as I will need the formula work after 17.05 as well..
And the second one somewhy makes my excel hang for 2 minutes))
any other suggestions?

Did you try all suggestions?

Here is a link on performance:

http://www.mrexcel.com/forum/showthread.php?t=292473

 

[kidwispa]

The formula I've given counts the number of unique dates within your column. Make sure you change the "A:A" bits to specifically match the range you wish to count... ie...

If your data range is A1-A10...

=sumproduct(1/countif(A1:A10,A1:A10))

Hope this helps

 

[stepan1987]

Thanks, this one works perfect!

 

[Aladin Akyurek]

Thanks, this one works perfect!

Which did you pick out?

 

[stepan1987]

Aladin AKyurek, I actually picked the first formula yoe ve posted, the misunderstanding is due to a deferred response, thanks!