2 Quick Ways To Calculate Distinct Count With Pivot Tables

[@jesslynnsh]

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!

[@leetan7511]

Thanks Jon, your Excel webinar was a real game-changer! The tips were incredibly helpful, and I'm excited to learn more about the Modern Excel Blueprint. Thanks again!

[@ExcelCampus]

Thank you for your feedback, Lee! 😀

[@abdallahbendriss2599]

Thanks body, you saved me a lot of trouble and you made my night, was working at 1AM :)

[@marcnaidu5804]

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.

[@ExcelCampus]

Thanks for the feedback, Marc! 😀

[@vinay_tire]

Thanks, I invested 30 minutes before reaching here... big help. Thanks

[@ExcelCampus]

Thanks for the feedback @vinay_tire 😀

[@lisaamante5384]

Las Vegas, Nevada, USA: Excellent! Quick, concise, clear, easy! Thanks so much also for explaining pros and cons of both ways.

[@KayYesYouTuber]

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.

[@luideveyra6259]

Thank you!!! Sending support from Philippines

[@vikasjoseph2008]

I have been struggling with distinct count for long. Thanks very much for this solution. Keep up the good work

[@IslamMuhammad]

Easy and smooth explanation, Thanks for helping

[@prabhurethinamsredmi6978]

Thank you for the detailed explanation. 2nd option fits my need, appreciate your help

[@rohitekka2674]

Armed with two new tricks, Thanks Jon!

[@ca.ravigoyal5782]

Thanks a ton from India Jon. Really Big problem is solved

[@JabastinS]

The way you have explained amazing. Thank you.

[@bungaranpanggabean1709]

Good work job Jon. Its working well for small number of data, but not efficient for big number of data

[@debabrataghosh8338]

Thanks for sharing the trick,Mr Jon .

[@foxiesh]

SUPER HELPFUL!! Thank you very much

[@nboisen]

Excellent explanation of the filter context advantage when using Power Pivot!!!

[@sherivanderhoof7106]

So Accurate - easy to use!! Easy to update with new data - So happy I found you!

[@ExcelCampus]

Glad it was helpful! 😀

[@Vlog88999]

Perfect teacher of excel

[@patriciadavis5299]

This was a great explanation! Thank you for the multiple options why one may be chosen over another.

[@ExcelCampus]

You're very welcome! 😀

[@SothearithKONGMrMuyKhmer]

Thanks. That’s really handy with distinct count in Pivot table. 🎉

[@ExcelCampus]

Glad it was helpful! 😀

[@haronnjiru8122]

Excellent explanation. Thank you

[@oyewoleismail2427]

Lifesaver. Thank you for sharing this video

[@sharidavis4888]

This helped me SO MUCH! Thank you Jon!

[@MyChannel706]

Excellent tutorial. Thanks much!! Very helpful.

[@H_a_n_z]

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

[@ExcelCampus]

Thanks for the feedback! 😀

[@jcop14]

Great instruction! Thank you.

[@ExcelCampus]

You are welcome! 😀

[@shubhamlale5946]

Awesome, I was looking for the 2nd solution Thank you very much

[@patricktampus4937]

This is VERY HELPFUL :) Thank you

[@cargouvu]

This is awesome. Helped me solve an issue in SQL... Thank you soo much!!

[@alconomic476]

Thanks, this was helpful and the presentation was great

[@pramodcomputertips7872]

I am Pramod from India. Thanks sir, your suggestion solved a big problem.

[@sachinrv1]

That's fantastic. Pivot table is always easy and wonderful tool. Thanks for sharing this trick :)

[@ExcelCampus]

Thanks Sachin! 🙂

[@Annethe71]

Thanks for this, it really saved my day and so well explained as always. Really like your tutorials.

[@kirrilywelsh]

Hi Jon: Your explanations are always so easy to follow. Cheers :)

[@ExcelCampus]

Thanks Kirrily! I appreciate your support. 🙌

[@marcnaidu5804]

@@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

[@aguerojg]

Thank you very much, Jon, your explanation is so clear and great, amazing solution with the Deal count column :)

[@abhilashponnam]

Thanks a lot! Was looking for it for quite some time. Your explanation was intuitive and easy

[@selinakarac6792]

This is very helpful, thanks!

[@akshitstenaa]

Loved it
It was super simple
Thx brother

[@ExcelCampus]

Most welcome 😊

[@hangtrinh7207]

Very useful video ..Thanks so much ..

[@wangecho7696]

thank you so much! exactly what I need!

[@ExcelCampus]

Glad it helped! 😀

[@vijaysahal4556]

Hi jon you are amazing 👍🏻👍🏻👍🏻👍🏻

[@rrgalvao]

Exactly what I needed! Thanks!!!

[@ExcelCampus]

Glad it helped! 😀

[@wayneedmondson1065]

Thanks Jon. Great video and solutions. Looking forward to Power Query and Dynamic Array function methods. Thumbs up!!

[@ExcelCampus]

Thanks Wayne! 👍

[@wayneedmondson1065]

@@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 :))

[@sallyho146]

@@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?

[@mkparker99]

This was awesome. thank you!

[@ExcelCampus]

Thanks, mkparker99! 😀

[@dastageera9626]

Thanks dear brother...... I lawsys waiting for your great videos.....

[@ExcelCampus]

Thanks Dastageer! I appreciate your support. 🙌

[@darrylmorgan]

Great Explanations..Thank You Jon :)

[@ExcelCampus]

Thanks Darryl! 🙌

[@Wulandari-gr2se]

Thanks a lot Jon.... this is really useful for my work

[@ExcelCampus]

Thanks! 🙂

[@doktorexcel8603]

Great solutions.

[@tonyjinca]

Thank you Sir!

[@gboyearo387]

God bless you...

[@dinorr8892]

Thanks Jon really nice sharing...

[@samiakram147]

For more than 10 years we have been using different and lengthy way this vdo gave a relief 😂

[@ExcelCampus]

Glad it helped! 😀

[@bador28]

It is a great solution, thanks so much

[@alexq3498]

Excellent. Thanks!

[@ExcelCampus]

You're welcome, Alex! 😀

[@ahsanhsakib1875]

Great Video

[@sunilkamati7224]

Sir
Your video is really helpful

[@ExcelCampus]

Thanks Sunil! 🙂

[@miriambonilla6407]

Thank you, I might use it some how in the future.

[@armaankhan5148]

Thank you so much

[@Luciano_mp]

Thanks Jon!

[@snacktics4771]

Thanks bud!

[@himkartech]

this is great thank you

[@zaririrani2494]

This knowledge helped in saving manual work. Thank You John!

[@onepercent3934]

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

[@alexscheleider2210]

thank you!

[@jaliali84]

Amazing 😍

[@lestariu509]

OMG Thank you

[@palanisubramani12]

Great video - How do we convert existing normal pivot table into Data Model Pivot Table / Power Pivot

[@lorenzobattistin1501]

Amazing! So simple but so powerful!

[@ExcelCampus]

It really is, thanks @lorenzobattistin1501! 😀

[@mohamadkuk]

Omg this is awesome

[@gerbherb8215]

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?

[@ExcelCampus]

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! 🙂

[@gerbherb8215]

@@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?

[@Henrich205]

Awesome :), thank you :)

[@NaveedBarq]

Brilliant

[@VRmatter-]

Still helping👍

[@claudiavalsecchi1593]

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)

[@sikkandhark]

Will power pivot table be connect with other pivot table with slicers?

[@shreediculous]

Great explanation.
How do we do this if we want to get counts of multiple columns?

[@jbodnar2012]

Why is distinct count only in Power Pivot?! Took me all day to find this, but hey, thank you for helping solve my issue!!

[@chess_and_tech]

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!

[@BharathBhushan-ei9ue]

cool trick

[@AkaExcel]

Hi! It will be also interesting if you show Solutions by your Commenters, Hope it Helps!

[@conradoiiaustria5277]

Can we do this then do a running total?

[@BenardNjuguna-w9d]

Awsome

[@MIMGURU]

How to calculate percent of win and loss can u bring video

[@adim7909]

Can we use this feature in MacBook ?

[@jhayg.serrano9349]

what if i add the product name above the sales stage in rows, will it work also?

[@zyngacita]

I get this to work, however when I track by month the Grand Total doesn't align with the individual month totals.

[@gibbyutah]

How do you get a list of the distinct items?

[@lakhandahiya3003]

It would be a great help for practice if you provide excel file too.

[@ExcelCampus]

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! 🙂

[@raunakjain7251]

Unable to add calculations measure for pivot tables when you use datamodel in pivot

[@masterexcel87]

How about if with blanks? How could we eliminate the blank count in the distinct count?

[@MrPhanisDav]

How to count a unique Number BUT Per Day? Thanks In Advanced

[@jesuseduardobittersuarez7517]

when I add new data to the model with the second option the table disolves , Help =(

[@PGJAS]

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.

[@chaddemeuse6760]

What if you're using large
datasets and you want to count say client names using by branch for a specific accounting period.

[@fe00700]

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

[@niteshtoshniwal8951]

With distinct count function, calculated item and calculated field options gets greyed out. Is there any option to bring both together?

[@luiscasas7219]

Hi Jon, if a cell is empty Excel counts it as a unique value, which is wrong, do you have a solution for that?

[@bintangmuhammad7082]

why is my distinct count not showing?

[@AbuEisaAyoub]

when you insert the "Pivot Table" > click yes on the last option that says "Add this data to the Data Model"