Count unique dates stored as text

[sharky12345]

I have a column of data which comprises of dates stored as text - I need to be able to count the number of unique dates in that range and have tried this;

=SUMPRODUCT(1/COUNTIF(Dates!A2:A50,A2:A50))

but it doesn't work, presumably because they're stored as text.

Any workaround?

 

[Marcelo Branco]

See if this works

=SUMPRODUCT(--(Dates!A2:A50<>""),1/COUNTIF(Dates!A2:A50,Dates!A2:A50&""))

I used Dates!A2:A50&"" to deal with blank cells (if they exist)

M.

 

[BobUmlas]

Are you doing this formula from a sheet other than the Dates sheet? Your formula may need to be
=SUMPRODUCT(1/COUNTIF(Dates!A2:A50,Dates!A2:A50))
and not
=SUMPRODUCT(1/COUNTIF(Dates!A2:A50,A2:A50))

Irrelevant that it's text.

 

[Johnny C]

=SUM(IF(FREQUENCY(MATCH(Dates!A2:A50,Dates!A2:A50,0),MATCH(Dates!A2:A50,Dates!A2:A50,0))>0,1))
it won't work if the range has blank cells in though

 

[Aladin Akyurek]

I have a column of data which comprises of dates stored as text - I need to be able to count the number of unique dates in that range and have tried this;



but it doesn't work, presumably because they're stored as text.

Any workaround?

One of:

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(Dates!A2:A50+0,Dates!A2:A50+0),1))

=SUM(IF(FREQUENCY(IF(1-(Dates!A2:A50=""),MATCH(Dates!A2:A50,Dates!A2:A50,0)),ROW(Dates!A2:A50)-ROW(Dates!A2)+1),1))

 

[sharky12345]

Guys, thanks to all who have replied, I'm sure one of these suggestions will work - I'll come back if they don't....