Count unique records... with a twist... Need help!

[phchenier]

Hi Everyone,

I need a little help on this...

Each row on colum C has a date in date format
The same date can appear more then once within the column
not all days within a month will necessarayl appear, so for August,
even tought it has 31 days, there might be 500 entries, but only
spanned across 10 different days.

Example of colum C:

Date
July 22, 2006
July 23, 2006
August 01, 2006
August 01, 2006
August 03, 2006
August 03, 2006
August 03, 2006
August 05, 2006
August 06, 2006

I need to count how many unique days within the month of august are
within this column, the example above has 4 days within august. How
would I go about doing this with a single formula? I have other ways of
calculating this but creating a table with all the days and then using
a countif, but that would be bukly and slow for something that has to
keep at least 1 year's worth of data.

 

[Duke Carey]

You can use two countif() functions

=countif(c1:c1000,">7/31/2006")-countif(c1:c1000,">8/31/2006")

 

[Bob Phillips]

=SUM(IF(FREQUENCY(IF((A2:A500<>"")*(MONTH(A2:A500)=8),A2:A50),IF((A2:A500<>"
")*(MONTH(A2:A500)=8),A2:A500))>0,1))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[David Billigmeier]

This will work, make sure to array enter it (CTRL+SHIFT+ENTER):

=SUM(--(FREQUENCY(IF(MONTH(C1:C1000)=8,MATCH(C1:C1000,C1:C1000,0)),ROW(INDIRECT("1:"&ROWS(C1:C1000))))>0))

 

[phchenier]

Works great...thanks !!!

=SUM(IF(FREQUENCY(IF((A2:A500<>"")*(MONTH(A2:A500)=8),A2:A50),IF((A2:A500<>"
")*(MONTH(A2:A500)=8),A2:A500))>0,1))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)