Pivot count excluding duplicates

**Tom_J_W** · 07-02-2013, 02:41 AM

Hello,

I'm trying to do some reporting of team members performance and have been having trouble only reptoring on Unique numbers. Basically what I would like is be able to this data for the count of unique application IDs per user by date but can't work out what to change to exclude duplicates from my reporting, is this possible or would I have to manually copy the data and then remove the duplicates?

P.S The data is pivoted in other ways so I would prefer not to change the actual data

Thanks,

Example.xlsx

**:) Sixthsense :)** · 07-02-2013, 02:51 AM

May be like this...

**Tom_J_W** · 07-02-2013, 03:09 AM

Hi Sixsense,

This works perfectly!

Can you explain the logic behind it to me please?

**:) Sixthsense :)** · 07-02-2013, 03:14 AM

I just used a supportive column (Column-I) in your base data and used the below formula

=IF(COUNTIFS($H$2:H2,H2,$A$2:A2,A2)=1,1,"")

The above formula check the Pivot Date and applicationId and mark the first instance of the data as 1 and rest are marked as nothing...

I just dropped that supportive column in the Value area of your pivot and marked it as Sum.

Thanks for the feedback