Count Unique Values in PivotTables in Excel
ฝัง
- เผยแพร่เมื่อ 19 ต.ค. 2024
- Count unique values in PivotTables in Excel using the new Distinct Count function.
When working with PivotTables the Count function will count all occurrences of a value. However, you may be trying to answer how many unique products were sold in a country, or how many unique customers are in that country.
This video looks at answering such questions by using the Distinct Count function in Excel PivotTables.
Find more great free tutorials at;
www.computerga...
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/Ultimat...
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1
9 years later and still valid! Thank you.
You're welcome!
I can't thank you enough!!! I have been sitting for entire day searching for just what you beautifully described. thanks again
Great to hear. Thank you very much.
9 years later, it still works. Thanks!
You're welcome! Great to hear.
i have been looking for this for weeks now. I agree w/ others below that said this is complicated before this feature. thanks a lot for the tutorial.
Even though it's very old tutorial, it was extremely helpful and saved lot of my time. thanks 🙂
Glad to hear that! You're welcome.
Life saver! Thank you for this. I started to work as a fraud analyst and this is very useful.
Finally!!! I've been waiting for this one. The formulas to do this in previous versions were awful. Thanks for sharing this new feature.
No problem Sandy. I love these beautiful new features
Can't thank you enough! this has been of huge help!
Great! You're very welcome.
This is crisp, precise and what i needed. you saved my day!
You're welcome 🙂
Oh dear!! All those sumproduct gymnastics couldn't do a thing for me. Thanks a ton for making this video.
😊 No worries, Kelvin.
Was looking for this solution for a very long time. Thank you for sharing
No problem 👍 Happy to help.
You saved me 45 minutes of a headache!! ! THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
You're welcome. Happy to help Ana.
This is great stuff! However I seem to have a problem after this, I can't seem to use the option "show items with no data". Just wondering if you know of a workaround this?
You saved my night!! Big Salute to you!!
You're welcome! Thank you 😊
you made my life so much easier. thanks
I enjoyed watching this video and recommend others to watch
Thanks for educating the community and appreciate your volunteership.
Keep posting some more videos.
Thanks a bunch
Simple and straightforward. Many thanks
You're welcome!
Thanks, I solved it with your video very quickly.
Awesome! Great to hear that.
Thank you for this video. It was a huge help.
Excellent. This worked when other recommendations did not. However, I do have a problem that came after. On the new pivot table created, I can calculate the unique count I need, but cannot then add a calculated field using that number. Any work around? Thank you
use GETPIVOTDATA, maybe
Lovely! Apt & cleanly explained. You really don't know how many you saved from getting a headache even after 7yr :)
Thank you 😊
AHHH Amazing, you just saved me soo much time!! Thank you!!
Excellent! Thank you, Lauren.
I have been searching for this one for long time.. finally.. it helped me a lot.. thank you sooo much
Exactly what I was looking for thank you!
Glad I could help!
Valuable lesson, thanks!
This video is really very helpful
Thank for sharing
Glad it was helpful!
Awesome! Thank you so much for sharing it!
My pleasure!
Wow! Thank you so much. I found great info here. 🙏
Awesome! Great to hear.
Very helpful! Thanks!
It's been almost 10 years but this video is still useful 👍🏼
Great to hear! Thank you.
You have been really helpful. Thankyou so much 😊. I am so grateful
My pleasure, Swati 👍
9 years later, still I must say thank you for the contribution! HAHAHA
Any time!
The year is 2023 and I just found this ancient solutions to my problem, THANK YOU Sir!!!
You're very welcome.
It helped me today. thank you
You're welcome!
Thanks for sharing... very helpful to me...
Great to hear 👍
Thank you very much!
Thanks a mill man. Really simplified a lot of things
Happy to help, Katlego. Thank you.
many, many thanks, my reporting head ache lasting for years resolved in 3:36min 🙂
Love it! You're very welcome.
oh my god !! Eventually it is there. You made my day, really.
Great to hear. Thanks Olivier.
you got real brains.....wow!!!!!!!
😊
Thanks - this is great - very clear tutorial
Thank You Man!!
You're very welcome!
Thanks for this, is going to save me hours
No problem 👍
Thanks a lot!!!! It help me to make show what I want in my PIVOT TABLE. Please let me know how I can share Coffe with you!!!
Excellent! Happy to help.
easy, nice and clear, thank you so much.
You're very welcome Ali 👍
You r awesome. I was being thinking how to do it. But after watched your video I cracked it..
Excellent 😊 Nice work Saurabh 💪
Very useful Thank you
You're very welcome, Mahalir.
THANK YOU!
You're welcome Joy.
Thanks very much for your tip !
You're very welcome. Thank you Vicente.
GENIUS! THANK YOU!!!!!
Thank you.
exactly what I was looking to do thanks
Great!!
Thank you! Very helpful
You're welcome Andrew. Thank you.
Really fantastic sir
Many many thanks Kiruba.
great tutorial, I used to know this then forgot and very hard to find it again just by randomly typing lol thanks a lot!
Glad I could help! Thank you 👍
Thanks really helpful
No worries. Thank you, Jarnail.
Your awesome! Thank you!
You're welcome, Lui 😊
You are God... Thank you so much
No worries.
very useful...
!!YOU DID IT!!
Thank you for sharing !
You're welcome Asambalah.
Thank you,
But the feature is not available in the excel for mac? 360
Excellent content
Thank you, Michael 👍
Very nice! I liked it
Thanks a lot!
Can't Thank You Enough
☺️
Thank you so much
You're welcome. Thank you, Ranjan.
Thank you so much !!!
You are welcome Sudhir, thank you.
Thank you
You're welcome
Thank you so much. very helpful
Ханж,анав
You're very welcome.
thanks for this helpful video
You are very welcome.
Thanks a lot!
You're welcome, Avishkar.
Very valuable
Thank you Renier.
Used distinct count but I want to see a trend of the distinct count each month. However, the total columns are ones (1) for each month with data. I want to know how many distinct customers bought that month. What did I do wrong? Thanks for the help.
Clicking the add data to model changed my afternoon lol
Oh yes!!!!
!! GREAT VIDEO !!
Thank you 😊
Life saver!
Happy to help, Karthik.
Thank YOU bro$
My pleasure. Thank you.
Wow super helped so much 👏
Awesome! Happy to help.
Thanks
No problem
THANK YOU SO MUCH!
You're very welcome Emma.
Thank you. I have Excel 2016. Why is my add to data model inactive / grayed out. Thank you.
SOLID! THANKS MAN~
My pleasure Bruno 👍
Man !! you rock !! awesome trick
Thank you Senthisurya.
Awesome thanks for the vid
Your welcome Ivo.
You saved my 8 hours of work🛐
Excellent! That is great.
thanks mate
No problem Kapil.
thanks!
You're welcome, Cristina.
Thanks alot
You're very welcome Sharier.
Awesome!!!
Thank you 👍
I agree with Sandy
Bro thank you so freaking much, division manager comes back tomorrow and I was supposed to do a report for him last week but I just couldn't for the life of me find out how to make the pivot table count unique values only once
Your welcome my friend.
Thx you
My pleasure. Thank you.
Thank youuu
Welcome 😊
genius!
I love this video ♥
Awesome! Thank you Hazem.
Is it possible to get same result in Excel 2010?
That function is not available to PivotTables in 2010 unfortunately. However there is always a way. This blog post will show you a method - www.computergaga.com/blog/count-unique-values-in-a-pivottable/
i have a problem with the grand total, it does not match when using Distinct count, can you throw some light on this please
hello, if I add a time element into columns and sales into value, how do I stop count of customer splitting by time and just stay as a total?
Sounds like you do not need the time in the Columns area. Putting it there should give you a row of column labels grouping the time by hour.
After setting the "Add this data to the Data Model" checkbox, excel does not allow to drill down (Double click) from the PIVOT. Any solution ?
What if I don't find that function of Distinct count?? How can add it to within my excel workbook? thanks!
You will need Excel 2013 or later and the data need to be loaded to the model for Distinct Count to appear.
@@Computergaga But I have Excel 2016, how do I do it to load data in the model? thanks a lot for your support!
It is demonstrated in the video Birasa.
I love you
😊
How do you break this out by quarter, month, or maybe even year? Is there one with unique values AND grouping?
+triple_12 In your PivotTable if you add the date column that you want to group into the PivotTables Rows area. Then right mouse click on a cell on the spreadsheet that has on eof the dates in it and choose Group. This will allow you to group by all the options you mention. The unique values will then be grouped.
Hi, After setting the "Add this data to the Data Model" checkbox, excel does not allow to group or ungroup, how should we go about?? Please suggest