Thanks for sharing the video! I have been trying to count the data in pivot table for days, until I saw your video, it solve my issue in 2mins! Really grateful!
@@ExcelCampus Hi Jon, will this work with no pivot table and using a =subtotal and count parameter to only count the unique values based on what is filtered? Thanks
@@ExcelCampus Thanks Jon. By the way, I modified my worksheet formula solution as follows: =SUM(--(LEFT(UNIQUE(Table1[Sales Stage]&"|"&Table1[Deal ID]),FIND("|",UNIQUE(Table1[Sales Stage]&"|"&Table1[Deal ID]))-1)=H4)). Don't need the SORT functions in there to get the correct result :))
@@ExcelCampus Jon - I have one apartment with two residents living there. If I want to count his many residents stay in each apartment by apartment # in one column and resident names in 2nd column, count in 3rd column, the count doesn’t combine 2 resident for apt #100, 105, etc because I put the resident names in column 2 using display feature. How can pivot table count 2 residents for apt #100, #105 etc?
Hi Jon, this video is very helpful! thank you so much for sharing this. I have a challenge when I'm trying on my Mac. When I create the pivot table, it does not show up the option "add this data to the data model"you mentioned in the video. I think I'm using the latest version of the software as I just subscribe the office 365 excel family , hope you may help advice. thank you
Thanks, well explained. I do still have a problem: the distinct count also adds up 1 for empty cells in a source table column. Can you tell me how to avoid that?
Hi G! 😊 Thank you for your kind feedback! One way the empty cells may show up is if they have a space in them. The space will count as a distinct row, which you could replace with nothing by matching the entire cell contents in the Find and Replace window (Ctrl+H). Another way to avoid the 1 for empty cells is to filter out blanks from that column in the PivotTable. I hope that helps! 🙂
@@ExcelCampus Hi Jon, Thank you very much for your quick answer. Unfortunately, it doesn't solve my problem and I will try to be clearer about it: the distinct count sees empty values (they do not contain spaces) as distinct values and so it adds 1 to the number of distinct values it finds. Technicaly this is correct, but many times you don't want this to happen, because these cells are empty because you don't (yet) know what value they should have. For example: you want to count the number of different brands of car tyres you have in a table. Then you don't want Excel to count empty cells as beiing one brand. Your second suggestion is not applicable, since there are no blank cells in the pivot table. Hope you can have a second look and maybe come with a solution?
THANK YOU! THIS IS SO HELPFUL. When I use Power Pivot for the Distinct count, how can I then create calculated fields? I need to calculate in the pivot the variation between 2 different distinct count (e.g. I need to calculate the variation of This year Vs Last year of distinct count ID Deal)
There is another way to do this with plain Pivot Tables. Create an entry in *both* the Value *and* the Rows or Columns where you want it. The Values will do the counting, and the Rows or Columns (whichever you want) will create entries for each item it identifies and you want counted. Cheers!
Saved my ass since macOS Excel does not support adding to data model... Thank you! Having grown in unix command line I find it hard to believe, that equivalent of | sort | uniq | wc -l can't be done in mac Excel at all
Hi Lakhan, Sorry about that. We forgot to include the solution file on the post. It is now available. You can find it in the Downloads section under the video on the following page. www.excelcampus.com/pivot-tables/distinctcount-pivot-tables/ Thanks again and have a nice day! 🙂
Hi, using calculated field in power Pivot I tried calculating count(column name).Just I need total number of rows....but it is always showing 1 in output.I tried with countA as well.But it didn't work...could you please help me with that.
I tried the COUNTIF method. Unfortunately, it outputs the count based on entire data upon applying filter also. Please have a solution that works with the filter too.
When I use data model and close the file with all working. I reopen the file and a refresh on the pivot crashes Excel; a sort on the pivot crashes excel; anything you do on the pivot using the model crashes excel. I have recreated this file twice; same results. I have used a different computer and got the same results. Any idea on how to fix this?
How to distinct two category in pivot table for 2010 excel . Example - Bank having error transactions A bank 2 customer having 8 transactions B bank 3 customer having 9 transaction C bank 1 customer having 1 transaction Like wise table need to get.please advice to get it from 2010 excel as a summery
Hello Sir, I have concern on this. It is indeed a great solution, however, I cant link this No.2 solution with other existing pivot. How can I go about this?
For many years I have had this problem and I would write the distinct query in SQL to do this. This simple trick is so helpful. Thanks a bunch.
Hi Jon, Excellent. Clear. And the window within the window is useful. Can see you explaining and the sheet at the same time. Thank you.
Thanks for the feedback, Marc! 😀
Thanks for sharing the video! I have been trying to count the data in pivot table for days, until I saw your video, it solve my issue in 2mins! Really grateful!
Las Vegas, Nevada, USA: Excellent! Quick, concise, clear, easy! Thanks so much also for explaining pros and cons of both ways.
The way you have explained amazing. Thank you.
Easy and smooth explanation, Thanks for helping
Thanks, I invested 30 minutes before reaching here... big help. Thanks
Thanks for the feedback @vinay_tire 😀
The first solution is beautiful ... easy to do and so smart ... thanks alot!! I tried that data model, but it makes problems with bigger tables
Thanks for the feedback! 😀
Thank you for the detailed explanation. 2nd option fits my need, appreciate your help
I am Pramod from India. Thanks sir, your suggestion solved a big problem.
I have been struggling with distinct count for long. Thanks very much for this solution. Keep up the good work
Good work job Jon. Its working well for small number of data, but not efficient for big number of data
Thanks for sharing the trick,Mr Jon .
This was a great explanation! Thank you for the multiple options why one may be chosen over another.
You're very welcome! 😀
Thank you!!! Sending support from Philippines
Thanks body, you saved me a lot of trouble and you made my night, was working at 1AM :)
Thanks. That’s really handy with distinct count in Pivot table. 🎉
Glad it was helpful! 😀
Excellent explanation of the filter context advantage when using Power Pivot!!!
Perfect teacher of excel
So Accurate - easy to use!! Easy to update with new data - So happy I found you!
Glad it was helpful! 😀
Excellent explanation. Thank you
Thanks a ton from India Jon. Really Big problem is solved
SUPER HELPFUL!! Thank you very much
Armed with two new tricks, Thanks Jon!
That's fantastic. Pivot table is always easy and wonderful tool. Thanks for sharing this trick :)
Thanks Sachin! 🙂
Lifesaver. Thank you for sharing this video
Great instruction! Thank you.
You are welcome! 😀
Thanks, this was helpful and the presentation was great
Excellent tutorial. Thanks much!! Very helpful.
This helped me SO MUCH! Thank you Jon!
Hi Jon: Your explanations are always so easy to follow. Cheers :)
Thanks Kirrily! I appreciate your support. 🙌
@@ExcelCampus Hi Jon, will this work with no pivot table and using a =subtotal and count parameter to only count the unique values based on what is filtered? Thanks
Thank you very much, Jon, your explanation is so clear and great, amazing solution with the Deal count column :)
Awesome, I was looking for the 2nd solution Thank you very much
Thanks for this, it really saved my day and so well explained as always. Really like your tutorials.
This is awesome. Helped me solve an issue in SQL... Thank you soo much!!
This knowledge helped in saving manual work. Thank You John!
For more than 10 years we have been using different and lengthy way this vdo gave a relief 😂
Glad it helped! 😀
This is VERY HELPFUL :) Thank you
Thanks a lot! Was looking for it for quite some time. Your explanation was intuitive and easy
Very useful video ..Thanks so much ..
This is very helpful, thanks!
Thanks Jon. Great video and solutions. Looking forward to Power Query and Dynamic Array function methods. Thumbs up!!
Thanks Wayne! 👍
@@ExcelCampus Thanks Jon. By the way, I modified my worksheet formula solution as follows: =SUM(--(LEFT(UNIQUE(Table1[Sales Stage]&"|"&Table1[Deal ID]),FIND("|",UNIQUE(Table1[Sales Stage]&"|"&Table1[Deal ID]))-1)=H4)). Don't need the SORT functions in there to get the correct result :))
@@ExcelCampus Jon - I have one apartment with two residents living there. If I want to count his many residents stay in each apartment by apartment # in one column and resident names in 2nd column, count in 3rd column, the count doesn’t combine 2 resident for apt #100, 105, etc because I put the resident names in column 2 using display feature. How can pivot table count 2 residents for apt #100, #105 etc?
thank you so much! exactly what I need!
Glad it helped! 😀
Hi Jon, this video is very helpful! thank you so much for sharing this. I have a challenge when I'm trying on my Mac. When I create the pivot table, it does not show up the option "add this data to the data model"you mentioned in the video. I think I'm using the latest version of the software as I just subscribe the office 365 excel family , hope you may help advice. thank you
Loved it
It was super simple
Thx brother
Most welcome 😊
Thanks a lot Jon.... this is really useful for my work
Thanks! 🙂
Exactly what I needed! Thanks!!!
Glad it helped! 😀
God bless you...
Thanks, well explained. I do still have a problem: the distinct count also adds up 1 for empty cells in a source table column. Can you tell me how to avoid that?
Hi G! 😊 Thank you for your kind feedback! One way the empty cells may show up is if they have a space in them. The space will count as a distinct row, which you could replace with nothing by matching the entire cell contents in the Find and Replace window (Ctrl+H). Another way to avoid the 1 for empty cells is to filter out blanks from that column in the PivotTable. I hope that helps! 🙂
@@ExcelCampus Hi Jon,
Thank you very much for your quick answer. Unfortunately, it doesn't solve my problem and I will try to be clearer about it: the distinct count sees empty values (they do not contain spaces) as distinct values and so it adds 1 to the number of distinct values it finds. Technicaly this is correct, but many times you don't want this to happen, because these cells are empty because you don't (yet) know what value they should have. For example: you want to count the number of different brands of car tyres you have in a table. Then you don't want Excel to count empty cells as beiing one brand.
Your second suggestion is not applicable, since there are no blank cells in the pivot table.
Hope you can have a second look and maybe come with a solution?
Thank you Sir!
This was awesome. thank you!
Thanks, mkparker99! 😀
Hi jon you are amazing 👍🏻👍🏻👍🏻👍🏻
Great solutions.
It is a great solution, thanks so much
Great video - How do we convert existing normal pivot table into Data Model Pivot Table / Power Pivot
Why is distinct count only in Power Pivot?! Took me all day to find this, but hey, thank you for helping solve my issue!!
Thank you, I might use it some how in the future.
Great Explanations..Thank You Jon :)
Thanks Darryl! 🙌
THANK YOU! THIS IS SO HELPFUL. When I use Power Pivot for the Distinct count, how can I then create calculated fields? I need to calculate in the pivot the variation between 2 different distinct count (e.g. I need to calculate the variation of This year Vs Last year of distinct count ID Deal)
Sir
Your video is really helpful
Thanks Sunil! 🙂
Thank you so much
Thanks Jon really nice sharing...
Thanks bud!
There is another way to do this with plain Pivot Tables. Create an entry in *both* the Value *and* the Rows or Columns where you want it. The Values will do the counting, and the Rows or Columns (whichever you want) will create entries for each item it identifies and you want counted. Cheers!
Thanks dear brother...... I lawsys waiting for your great videos.....
Thanks Dastageer! I appreciate your support. 🙌
this is great thank you
Excellent. Thanks!
You're welcome, Alex! 😀
Great Video
thank you!
Thanks Jon!
Amazing! So simple but so powerful!
It really is, thanks @lorenzobattistin1501! 😀
OMG Thank you
Still helping👍
Great explanation.
How do we do this if we want to get counts of multiple columns?
Saved my ass since macOS Excel does not support adding to data model... Thank you! Having grown in unix command line I find it hard to believe, that equivalent of | sort | uniq | wc -l can't be done in mac Excel at all
Hi! It will be also interesting if you show Solutions by your Commenters, Hope it Helps!
Omg this is awesome
Brilliant
Amazing 😍
Awesome :), thank you :)
How to calculate percent of win and loss can u bring video
How would you calculate the row %change in this pivot table for the distinct count?
Awsome
I get this to work, however when I track by month the Grand Total doesn't align with the individual month totals.
Will power pivot table be connect with other pivot table with slicers?
It would be a great help for practice if you provide excel file too.
Hi Lakhan,
Sorry about that. We forgot to include the solution file on the post. It is now available. You can find it in the Downloads section under the video on the following page. www.excelcampus.com/pivot-tables/distinctcount-pivot-tables/
Thanks again and have a nice day! 🙂
How to count a unique Number BUT Per Day? Thanks In Advanced
Can we do this then do a running total?
What if you're using large
datasets and you want to count say client names using by branch for a specific accounting period.
Hi, using calculated field in power Pivot I tried calculating count(column name).Just I need total number of rows....but it is always showing 1 in output.I tried with countA as well.But it didn't work...could you please help me with that.
Can we use this feature in MacBook ?
what if i add the product name above the sales stage in rows, will it work also?
How about if with blanks? How could we eliminate the blank count in the distinct count?
Unable to add calculations measure for pivot tables when you use datamodel in pivot
I tried the COUNTIF method. Unfortunately, it outputs the count based on entire data upon applying filter also. Please have a solution that works with the filter too.
When I use data model and close the file with all working. I reopen the file and a refresh on the pivot crashes Excel; a sort on the pivot crashes excel; anything you do on the pivot using the model crashes excel. I have recreated this file twice; same results. I have used a different computer and got the same results. Any idea on how to fix this?
Hi Jon, if a cell is empty Excel counts it as a unique value, which is wrong, do you have a solution for that?
How to distinct two category in pivot table for 2010 excel .
Example - Bank having error transactions
A bank 2 customer having 8 transactions
B bank 3 customer having 9 transaction
C bank 1 customer having 1 transaction
Like wise table need to get.please advice to get it from 2010 excel as a summery
With distinct count function, calculated item and calculated field options gets greyed out. Is there any option to bring both together?
Hello Sir, I have concern on this. It is indeed a great solution, however, I cant link this No.2 solution with other existing pivot. How can I go about this?