Counting unique values in date range

[magnus101]

Hello,
I am trying to count unique values in a date range.
I found this great sample:
How to count unique distinct records in a date range | Get Digital Help - Microsoft Excel resource

However my date range comes from a pivot table grouped by 7 days.

So my data is like:

Date, Item

then I put this into a pivot table. With the date having a 7 day grouping.

So my dates are like

1/9/2012 - 1/15/2012

1/16/2012 - 1/22/2012

<colgroup><col style="mso-width-source:userset;mso-width-alt:5851;width:120pt" width="160"> </colgroup><tbody>
</tbody>

Is there an easy way of putting that date range into the formula from the solution page posted above?
Or better yet,
Is there a way to have the pivot table count unique values right in itself?
Maybe by adding a calculated field?

thanks

 

[Aladin Akyurek]

Hello,
I am trying to count unique values in a date range.
I found this great sample:
How to count unique distinct records in a date range | Get Digital Help - Microsoft Excel resource

However my date range comes from a pivot table grouped by 7 days.

So my data is like:

Date, Item

then I put this into a pivot table. With the date having a 7 day grouping.

So my dates are like

1/9/2012 - 1/15/2012

1/16/2012 - 1/22/2012

<tbody>
</tbody>

Is there an easy way of putting that date range into the formula from the solution page posted above?
Or better yet,
Is there a way to have the pivot table count unique values right in itself?
Maybe by adding a calculated field?

thanks

Are you trying to count distinct dates, i.e., determine the number distinct/unique dates? If so, you could use directly the source data.

 

[circledchicken]

Or better yet,
Is there a way to have the pivot table count unique values right in itself?
Maybe by adding a calculated field?

thanks

Hi,

You can't do it directly in a traditional PivotTable - but you can do it indirectly by adding a unique count helper column in your source data. The easiest method however may be to use a PowerPivot measure for Excel 2010+. Both methods are explained here:

• Unique Count in Excel Pivot Table With PowerPivot