OK... I know half the questions posted to this forum seem to involve counting unique values, but I can't find any that fit my scenario.
I have a large dataset whose records include a date (mmddyyyy) field. For each month-day combination (mmdd, irrespective of year), I'd like to count the number of unique YEARS having records associated with that month-day combination.
I know I could do this (probably, with your help) with a set of array formulas, but I've got a lot of records and other formulas in this sheet already, so I'd rather not add another set of computationally intense formulas.
Rather, I'd like to obtain the information thru a Pivot Table. I can kinda get the answer indirectly by counting the number of rows returned in the Pivot Table, but there has to be a better way. See example for what the Pivot Table currently looks like, plus what I REALLY want....
Excel 2007
I suspect that the Pivot Table can't yield (directly) what I want, since it summarizes records, not groupings of records. Tho it is probably way below your standards for elegant solutions, I'd settle for some advice on how to count the "detail" rows in the Pivot Table to get the data I need. Even that ugly approach is probably better than further encumbering my spreadsheet with an elegant set of array formulas.
Thanks in advance!
SDL
I have a large dataset whose records include a date (mmddyyyy) field. For each month-day combination (mmdd, irrespective of year), I'd like to count the number of unique YEARS having records associated with that month-day combination.
I know I could do this (probably, with your help) with a set of array formulas, but I've got a lot of records and other formulas in this sheet already, so I'd rather not add another set of computationally intense formulas.
Rather, I'd like to obtain the information thru a Pivot Table. I can kinda get the answer indirectly by counting the number of rows returned in the Pivot Table, but there has to be a better way. See example for what the Pivot Table currently looks like, plus what I REALLY want....
Excel Workbook | |||||||
---|---|---|---|---|---|---|---|
C | D | E | F | G | |||
4 | What I've got | Pivot Table. | |||||
5 | |||||||
6 | Row Labels | Count of StartDate | Interpretation | ||||
7 | 1/4 | 1 | Summary: There was a total of 1 record with a date of 1/4 (irrespective of year) | ||||
8 | 1/4/2004 | 1 | Detail: There was 1 record with the date 1/4/2004. | ||||
9 | 1/7 | 2 | |||||
10 | 1/7/2001 | 1 | |||||
11 | 1/7/2004 | 1 | |||||
12 | 1/12 | 3 | |||||
13 | 1/12/2009 | 3 | |||||
14 | 2/9 | 5 | Summary: There was a total of 5 records with a date of 2/9 (irrespective of year) | ||||
15 | 2/9/2002 | 2 | Detail: There were 2 records with the date 2/9/2002 | ||||
16 | 2/9/2006 | 3 | Detail: There were 3 records with the date 2/9/2006 | ||||
17 | |||||||
18 | |||||||
19 | What I want | Freq Table | |||||
20 | |||||||
21 | Start-Mo-Day | CountUnique | Interpretation | ||||
22 | 1/4 | 1 | Of all the records with date 1/4, there was only 1 unique year represented. | ||||
23 | 1/7 | 2 | Of all the records with date 1/7, there were 2 unique years represented | ||||
24 | 1/12 | 1 | Of all the records with date 1/12, there was only 1 unique year represented. | ||||
25 | 2/9 | 2 | Of all the records with date 2/9, there were only 2 unique years represented. | ||||
26 | |||||||
27 | Essentially, the value associated with each date is a count of the number of | ||||||
28 | "detail" rows from the pivot table above. | ||||||
Sheet1 |
I suspect that the Pivot Table can't yield (directly) what I want, since it summarizes records, not groupings of records. Tho it is probably way below your standards for elegant solutions, I'd settle for some advice on how to count the "detail" rows in the Pivot Table to get the data I need. Even that ugly approach is probably better than further encumbering my spreadsheet with an elegant set of array formulas.
Thanks in advance!
SDL