One field in the pivot table has to bring up unique values

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

One field in the pivot table has to bring up unique values

Post by BittenApple »

Hello team,
I have inserted a pivot table. One column has duplicated values, which I don't want to get any calculation to be done on. It seems pivot table can't do it.

What is the work around this? Should I go to source data and delete all duplicated values? Are there any functions such as sumif or sumifs to bring unique values?

What is the solution?

Regards,
Bitten

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: One field in the pivot table has to bring up unique values

Post by HansV »

Please explain in more detail what you want.
Best wishes,
Hans

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: One field in the pivot table has to bring up unique values

Post by BittenApple »

Hello team,

Please review the attachment. I inserted a pivot table on the attached. Column all needs to be unique. It seems that excel can't give us some unique values.

Regards,
bita
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: One field in the pivot table has to bring up unique values

Post by HansV »

You can COUNT unique values in a pivot table, but not SUM unique values.
However, you can use a combination of SUM, UNIQUE and IF - see the attached version.

SumOfTheUnique.xlsx
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: One field in the pivot table has to bring up unique values

Post by BittenApple »

Hello Hans,
Thank you so much for the response.

Can you please explain about this _xlfn?

Would it be correct If I put average for those fields in the pivot table that you calculated as unique?

Respectfully,
Bita

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: One field in the pivot table has to bring up unique values

Post by HansV »

The formula in my previous reply only works if you have Microsoft 365 or Office 2019. It won't work in older versions.
Here is a workbook that will work in all versions of Excel.

SumOfTheUnique.xlsx
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans