Counting unique values by date

[saylur]

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(B1: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!!!

 

[Teethless mama]

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

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

ctrl+shift+enter, not just enter

 

[Duke Carey]

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.

 

[T. Valko]

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)

 

[saylur]

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

 

[saylur]

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.

 

[saylur]

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

 

[T. Valko]

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 Data>Filter>Advanced 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.

 

[Balan]

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.

 

[Balan]

Sorry, what I posted doesn't work correctly.

 

[Balan]

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