Unique Date Values

[tommyfernandez]

Greetings,

Please advise on how to count unique date values (e.g., 2/4/07, 2/4/07, 2/5/07, 2/6/07 = 3 unique values).

I'm working on a spreadsheet which has a date column with duplicate dates. I need to count the number of unique dates.

Your help is greatly appreciated!!!

Tommy

[Mark@Work]

Assuming your dates ar in Column B starting at Cell B1

C1 : =B1
C2 : =IF(ISNUMBER(MATCH(B2,B$1:B1,0)),"",B2) : replicate down thus
C3 : =IF(ISNUMBER(MATCH(B3,B$1:B2,0)),"",B3)
etc..
In words this translates to :
If there is a Match found further up the list, put in a blank, otherwise put in the value.


Then =COUNT(C:C) will give you the number of unique values.

[Ron Coderre]

With
A1:A20 containing a list of dates

This formula returns the count of unique numbers (dates in this case)

=SUM(N(FREQUENCY(A1:A20,A1:A20)>0))

Note: That formula is durable against blanks or text in the source range.

Does that help?

[tommyfernandez]

Gentlemen,

Thanks for the prompt responses! I wasn't able to get Mark's formula to work (still working at it), but was able to use Rod's formula, however, only when I convert 7/5, 7/6, 7/6 to numbers.

Is there a way to work this using a Date format?

Tommy

[Ron Coderre]

To Excel....Dates ARE just numbers. Each date is represented as its number of days since 31-DEC-1899. For example, to Excel, 24-AUG-2007 is 39,318.

However, it appears that what you are calling "dates" are not really dates, but numeric text. The FREQUENCY function only works with numbers.

Consequently, try this formula to count unique values.
=SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))

Does that help?

[Teethless mama]

To Excel....Dates ARE just numbers. Each date is represented as its number of days since 31-DEC-1899. For example, to Excel, 24-AUG-2007 is 39,318.

However, it appears that what you are calling "dates" are not really dates, but numeric text. The FREQUENCY function only works with numbers.

Consequently, try this formula to count unique values.
=SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))

Does that help?

It's not TRUE. Frequency function also work with "TEXT", but use in different approach not like the one in post #3


It use like this:

=SUM(SIGN(FREQUENCY(IF(A1:A20<>"",MATCH(A1:A20&"",A1:A20&"",0)),MATCH(A1:A20&"",A1:A20&"",0))))

Array formula: commit with Ctrl+Shift+Enter, not just Enter

[AmateaurGeek]

With
A1:A20 containing a list of dates

This formula returns the count of unique numbers (dates in this case)

=SUM(N(FREQUENCY(A1:A20,A1:A20)>0))

Note: That formula is durable against blanks or text in the source range.

Does that help?

Amazing! Thanks! This task had been damaging the brick wall I had been banging my head off trying to solve it!