I know there have been similar posts about counting, but my twist is that I
need to count unique ocurrences of text in a cell for each date.

I have 18,000 rows of data, each with a date and a name. There are about 20
unique names in the entire sheet, and they appear on different dates. For
instance, there might be 300 rows for with a date of 12/15/06, but only 6
unique names within those 300 rows. I need to count how many unique names
appear on any given date, then show the count by date.

From the online help, I have this formula to count unique ocurrences, and it
works fine:

=SUM(IF(FREQUENCY(MATCH(B1:B370,B1:B370,0),MATCH(B 1:B370,B1:B370,0))0,1))

I just need to display the result for for each date within my sheet.

Any help on this? I can't figure it out.

Thanks!!!

Date & Name are defined name ranges
c1: holds criteria date

=SUM(N(FREQUENCY(IF(Date=C1,MATCH(Name,Name,0)),MA TCH(Name,Name,0))0))

ctrl+shift+enter, not just enter


"saylur" wrote:

I know there have been similar posts about counting, but my twist is that I
need to count unique ocurrences of text in a cell for each date.

I have 18,000 rows of data, each with a date and a name. There are about 20
unique names in the entire sheet, and they appear on different dates. For
instance, there might be 300 rows for with a date of 12/15/06, but only 6
unique names within those 300 rows. I need to count how many unique names
appear on any given date, then show the count by date.

From the online help, I have this formula to count unique ocurrences, and it
works fine:

=SUM(IF(FREQUENCY(MATCH(B1:B370,B1:B370,0),MATCH(B 1:B370,B1:B370,0))0,1))

I just need to display the result for for each date within my sheet.

Any help on this? I can't figure it out.

Thanks!!!

You can kill lots of brain cells trying to do this with formulas, but you're
probably best served by doing a pivot table with your data. Put the dates
down the column and the names across the top, then use COUNT(names) as your
data member. For any given date the answer is the COUNT of values across
that date's row.

"saylur" wrote:

I know there have been similar posts about counting, but my twist is that I
need to count unique ocurrences of text in a cell for each date.

I have 18,000 rows of data, each with a date and a name. There are about 20
unique names in the entire sheet, and they appear on different dates. For
instance, there might be 300 rows for with a date of 12/15/06, but only 6
unique names within those 300 rows. I need to count how many unique names
appear on any given date, then show the count by date.

From the online help, I have this formula to count unique ocurrences, and it
works fine:

=SUM(IF(FREQUENCY(MATCH(B1:B370,B1:B370,0),MATCH(B 1:B370,B1:B370,0))0,1))

I just need to display the result for for each date within my sheet.

Any help on this? I can't figure it out.

Thanks!!!

Try this array formula** :

A1 = some date

=COUNT(1/FREQUENCY(IF(date=A1,MATCH(name,name,0)),ROW(name)-MIN(ROW(name))+1))

Does not account for empty cells in the name range (causes an error).

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"saylur" wrote in message
...
I know there have been similar posts about counting, but my twist is that I
need to count unique ocurrences of text in a cell for each date.

I have 18,000 rows of data, each with a date and a name. There are about
20
unique names in the entire sheet, and they appear on different dates. For
instance, there might be 300 rows for with a date of 12/15/06, but only 6
unique names within those 300 rows. I need to count how many unique names
appear on any given date, then show the count by date.

From the online help, I have this formula to count unique ocurrences, and
it
works fine:

=SUM(IF(FREQUENCY(MATCH(B1:B370,B1:B370,0),MATCH(B 1:B370,B1:B370,0))0,1))

I just need to display the result for for each date within my sheet.

Any help on this? I can't figure it out.

Thanks!!!

Thanks! What about doing the same not for just one date, but for each of the
dates appearing in the sheet, or a specified range of dates?

Also, what does the 'name' refer to below? Is it some variable (i.e., the
particular name I'm looking for?) I need to know the count of all the names
appearing in the date(s).

"T. Valko" wrote:

Try this array formula** :

A1 = some date

=COUNT(1/FREQUENCY(IF(date=A1,MATCH(name,name,0)),ROW(name)-MIN(ROW(name))+1))

Does not account for empty cells in the name range (causes an error).

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"saylur" wrote in message
...
I know there have been similar posts about counting, but my twist is that I
need to count unique ocurrences of text in a cell for each date.

I have 18,000 rows of data, each with a date and a name. There are about
20
unique names in the entire sheet, and they appear on different dates. For
instance, there might be 300 rows for with a date of 12/15/06, but only 6
unique names within those 300 rows. I need to count how many unique names
appear on any given date, then show the count by date.

From the online help, I have this formula to count unique ocurrences, and
it
works fine:

=SUM(IF(FREQUENCY(MATCH(B1:B370,B1:B370,0),MATCH(B 1:B370,B1:B370,0))0,1))

I just need to display the result for for each date within my sheet.

Any help on this? I can't figure it out.

Thanks!!!

The pivottable won't work. I've tried that. It gives the total number of
times the name appears on a given day, not the count of the unique names on
that day.

"Duke Carey" wrote:

You can kill lots of brain cells trying to do this with formulas, but you're
probably best served by doing a pivot table with your data. Put the dates
down the column and the names across the top, then use COUNT(names) as your
data member. For any given date the answer is the COUNT of values across
that date's row.

"saylur" wrote:

I know there have been similar posts about counting, but my twist is that I
need to count unique ocurrences of text in a cell for each date.

I have 18,000 rows of data, each with a date and a name. There are about 20
unique names in the entire sheet, and they appear on different dates. For
instance, there might be 300 rows for with a date of 12/15/06, but only 6
unique names within those 300 rows. I need to count how many unique names
appear on any given date, then show the count by date.

From the online help, I have this formula to count unique ocurrences, and it
works fine:

=SUM(IF(FREQUENCY(MATCH(B1:B370,B1:B370,0),MATCH(B 1:B370,B1:B370,0))0,1))

I just need to display the result for for each date within my sheet.

Any help on this? I can't figure it out.

Thanks!!!

Even easier is putting the date, then the name in the colum, then putting the
name in the data section and count the names. That shows each name that
appears in that date, and the number of times it appears, but I need to see
the **number** of unique names that appear on each date.

If I could somehow count the number of names I see in that pivottable using
a function (rather than manually) . . .

"Duke Carey" wrote:

You can kill lots of brain cells trying to do this with formulas, but you're
probably best served by doing a pivot table with your data. Put the dates
down the column and the names across the top, then use COUNT(names) as your
data member. For any given date the answer is the COUNT of values across
that date's row.

"saylur" wrote:

I know there have been similar posts about counting, but my twist is that I
need to count unique ocurrences of text in a cell for each date.

I have 18,000 rows of data, each with a date and a name. There are about 20
unique names in the entire sheet, and they appear on different dates. For
instance, there might be 300 rows for with a date of 12/15/06, but only 6
unique names within those 300 rows. I need to count how many unique names
appear on any given date, then show the count by date.

From the online help, I have this formula to count unique ocurrences, and it
works fine:

=SUM(IF(FREQUENCY(MATCH(B1:B370,B1:B370,0),MATCH(B 1:B370,B1:B370,0))0,1))

I just need to display the result for for each date within my sheet.

Any help on this? I can't figure it out.

Thanks!!!

For each unique date...

You'd need to list those unique dates. One way to do that is to use the
Advanced Filter to extract the uniques and copy them to another location.
Then you'd just use the same formula and reference each unique date cell.

To extract the uniques using the Advanced filter:

Select the range of dates. Assume this range is A1:A100 with cell A1 being
your column header.
Goto the menu DataFilterAdvanced Filter
Select: Copy to another location
List Range: A1:A100
Copy to: enter a cell reference where you want the uniques to be copied to,
say, J1
Check: Unique records only
OK

Then enter the formula in cell K2 and copy down as needed.

=COUNT(1/FREQUENCY(IF(date=K2,MATCH(name,name,0)),ROW(name)-MIN(ROW(name))+1))

what does the 'name' refer to

Both "date" and "name" are just placeholders for your actual ranges. Replace
them with the appropriate range references.

For a specified range of dates:

enter the start and stop dates in a couple of cells:

D1 = start date
E1 = end date

Then:

=COUNT(1/FREQUENCY(IF((date=D1)*(date<=E1),MATCH(name,name ,0)),ROW(name)-MIN(ROW(name))+1))

Don't forget: these are array formulas.


--
Biff
Microsoft Excel MVP


"saylur" wrote in message
...
Thanks! What about doing the same not for just one date, but for each of
the
dates appearing in the sheet, or a specified range of dates?

Also, what does the 'name' refer to below? Is it some variable (i.e., the
particular name I'm looking for?) I need to know the count of all the
names
appearing in the date(s).

"T. Valko" wrote:

Try this array formula** :

A1 = some date

=COUNT(1/FREQUENCY(IF(date=A1,MATCH(name,name,0)),ROW(name)-MIN(ROW(name))+1))

Does not account for empty cells in the name range (causes an error).

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"saylur" wrote in message
...
I know there have been similar posts about counting, but my twist is
that I
need to count unique ocurrences of text in a cell for each date.

I have 18,000 rows of data, each with a date and a name. There are
about
20
unique names in the entire sheet, and they appear on different dates.
For
instance, there might be 300 rows for with a date of 12/15/06, but only
6
unique names within those 300 rows. I need to count how many unique
names
appear on any given date, then show the count by date.

From the online help, I have this formula to count unique ocurrences,
and
it
works fine:

=SUM(IF(FREQUENCY(MATCH(B1:B370,B1:B370,0),MATCH(B 1:B370,B1:B370,0))0,1))

I just need to display the result for for each date within my sheet.

Any help on this? I can't figure it out.

Thanks!!!

Saylur,
Please see whether this meets your requirement:

=SUMPRODUCT(--(name<"")*(date<"")*(date=A2))

where,
"name" is the column range containing names in the data range
"date" is the date range
A2 is the cell where you have the date for which you want the number of
unique names.

Copy this formula down for the other dates

Please confirm if it works.




"saylur" wrote:

I know there have been similar posts about counting, but my twist is that I
need to count unique ocurrences of text in a cell for each date.

I have 18,000 rows of data, each with a date and a name. There are about 20
unique names in the entire sheet, and they appear on different dates. For
instance, there might be 300 rows for with a date of 12/15/06, but only 6
unique names within those 300 rows. I need to count how many unique names
appear on any given date, then show the count by date.

From the online help, I have this formula to count unique ocurrences, and it
works fine:

=SUM(IF(FREQUENCY(MATCH(B1:B370,B1:B370,0),MATCH(B 1:B370,B1:B370,0))0,1))

I just need to display the result for for each date within my sheet.

Any help on this? I can't figure it out.

Thanks!!!

Sorry, what I posted doesn't work correctly.



"saylur" wrote:

I know there have been similar posts about counting, but my twist is that I
need to count unique ocurrences of text in a cell for each date.

I have 18,000 rows of data, each with a date and a name. There are about 20
unique names in the entire sheet, and they appear on different dates. For
instance, there might be 300 rows for with a date of 12/15/06, but only 6
unique names within those 300 rows. I need to count how many unique names
appear on any given date, then show the count by date.

From the online help, I have this formula to count unique ocurrences, and it
works fine:

=SUM(IF(FREQUENCY(MATCH(B1:B370,B1:B370,0),MATCH(B 1:B370,B1:B370,0))0,1))

I just need to display the result for for each date within my sheet.

Any help on this? I can't figure it out.

Thanks!!!

Sorry, what I posted just now doesn't work properly.

"saylur" wrote:

I know there have been similar posts about counting, but my twist is that I
need to count unique ocurrences of text in a cell for each date.

I have 18,000 rows of data, each with a date and a name. There are about 20
unique names in the entire sheet, and they appear on different dates. For
instance, there might be 300 rows for with a date of 12/15/06, but only 6
unique names within those 300 rows. I need to count how many unique names
appear on any given date, then show the count by date.

From the online help, I have this formula to count unique ocurrences, and it
works fine:

=SUM(IF(FREQUENCY(MATCH(B1:B370,B1:B370,0),MATCH(B 1:B370,B1:B370,0))0,1))

I just need to display the result for for each date within my sheet.

Any help on this? I can't figure it out.

Thanks!!!