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

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.

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

You can use two countif() functions

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


" wrote:

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.

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

=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)

wrote in message
ups.com...
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.

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

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))

--
Regards,
Dave


" wrote:

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.

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

Works great...thanks !!!
Bob Phillips wrote:
=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)

wrote in message
ups.com...
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.