Counting unique values by date
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!!!
|