Count unique dates (irrespective of year) via Pivot Table

SLARRA

Board Regular
Joined
Sep 22, 2007
Messages
93
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 Workbook
CDEFG
4What I've gotPivot Table.
5
6Row LabelsCount of StartDateInterpretation
71/41Summary: There was a total of 1 record with a date of 1/4 (irrespective of year)
81/4/20041Detail: There was 1 record with the date 1/4/2004.
91/72
101/7/20011
111/7/20041
121/123
131/12/20093
142/95Summary: There was a total of 5 records with a date of 2/9 (irrespective of year)
152/9/20022Detail: There were 2 records with the date 2/9/2002
162/9/20063Detail: There were 3 records with the date 2/9/2006
17
18
19What I wantFreq Table
20
21Start-Mo-DayCountUniqueInterpretation
221/41Of all the records with date 1/4, there was only 1 unique year represented.
231/72Of all the records with date 1/7, there were 2 unique years represented
241/121Of all the records with date 1/12, there was only 1 unique year represented.
252/92Of all the records with date 2/9, there were only 2 unique years represented.
26
27Essentially, the value associated with each date is a count of the number of
28"detail" rows from the pivot table above.
Sheet1
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Forum statistics

Threads
1,224,598
Messages
6,179,818
Members
452,946
Latest member
JoseDavid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top