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
One field in the pivot table has to bring up unique values
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
-
- Administrator
- Posts: 78608
- 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
Please explain in more detail what you want.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: One field in the pivot table has to bring up unique values
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
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.
-
- Administrator
- Posts: 78608
- 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
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.
However, you can use a combination of SUM, UNIQUE and IF - see the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: One field in the pivot table has to bring up unique values
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
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
-
- Administrator
- Posts: 78608
- 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
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.
Here is a workbook that will work in all versions of Excel.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans