2 Quick Ways To Calculate Distinct Count With Pivot Tables

[@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! 😀

[@prabhurethinamsredmi6978]

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

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

[@JabastinS]

The way you have explained amazing. Thank you.

[@IslamMuhammad]

Easy and smooth explanation, Thanks for helping

[@debabrataghosh8338]

Thanks for sharing the trick,Mr Jon .

[@nboisen]

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

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

[@SothearithKONGMrMuyKhmer]

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

[@ExcelCampus]

Glad it was helpful! 😀

[@patriciadavis5299]

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

[@ExcelCampus]

You're very welcome! 😀

[@rohitekka2674]

Armed with two new tricks, Thanks Jon!

[@vikasjoseph2008]

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

[@haronnjiru8122]

Excellent explanation. Thank you

[@luideveyra6259]

Thank you!!! Sending support from Philippines

[@alconomic476]

Thanks, this was helpful and the presentation was great

[@bungaranpanggabean1709]

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

[@abdallahbendriss2599]

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

[@foxiesh]

SUPER HELPFUL!! Thank you very much

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

[@MyChannel706]

Excellent tutorial. Thanks much!! Very helpful.

[@vinay_tire]

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

[@ExcelCampus]

Thanks for the feedback @vinay_tire 😀

[@sharidavis4888]

This helped me SO MUCH! Thank you Jon!

[@sachinrv1]

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

[@ExcelCampus]

Thanks Sachin! 🙂

[@aguerojg]

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

[@Annethe71]

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

[@jcop14]

Great instruction! Thank you.

[@ExcelCampus]

You are welcome! 😀

[@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! 😀

[@oyewoleismail2427]

Lifesaver. Thank you for sharing this video

[@shubhamlale5946]

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

[@cargouvu]

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

[@abhilashponnam]

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

[@Wulandari-gr2se]

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

[@ExcelCampus]

Thanks! 🙂

[@ca.ravigoyal5782]

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

[@patricktampus4937]

This is VERY HELPFUL :) Thank you

[@selinakarac6792]

This is very helpful, thanks!

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

[@pramodcomputertips7872]

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

[@miriambonilla6407]

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

[@doktorexcel8603]

Great solutions.

[@hangtrinh7207]

Very useful video ..Thanks so much ..

[@wangecho7696]

thank you so much! exactly what I need!

[@ExcelCampus]

Glad it helped! 😀

[@bador28]

It is a great solution, thanks so much

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

[@tonyjinca]

Thank you Sir!

[@mkparker99]

This was awesome. thank you!

[@ExcelCampus]

Thanks, mkparker99! 😀

[@dinorr8892]

Thanks Jon really nice sharing...

[@akshitstenaa]

Loved it It was super simple Thx brother

[@ExcelCampus]

Most welcome 😊

[@vijaysahal4556]

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

[@darrylmorgan]

Great Explanations..Thank You Jon :)

[@ExcelCampus]

Thanks Darryl! 🙌

[@samiakram147]

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

[@ExcelCampus]

Glad it helped! 😀

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

[@dastageera9626]

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

[@ExcelCampus]

Thanks Dastageer! I appreciate your support. 🙌

[@rrgalvao]

Exactly what I needed! Thanks!!!

[@ExcelCampus]

Glad it helped! 😀

[@zaririrani2494]

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

[@kulbeersingh4059]

Thank you very much.

[@alexq3498]

Excellent. Thanks!

[@ExcelCampus]

You're welcome, Alex! 😀

[@gboyearo387]

God bless you...

[@Luciano_mp]

Thanks Jon!

[@himkartech]

this is great thank you

[@sunilkamati7224]

Sir Your video is really helpful

[@ExcelCampus]

Thanks Sunil! 🙂

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

[@ahsanhsakib1875]

Great Video

[@alexscheleider2210]

thank you!

[@lorenzobattistin1501]

Amazing! So simple but so powerful!

[@ExcelCampus]

It really is, thanks @lorenzobattistin1501! 😀

[@palanisubramani12]

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

[@snacktics4771]

Thanks bud!

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

[@VRmatter-]

Still helping👍

[@shreediculous]

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

[@lestariu509]

OMG Thank you

[@AkaExcel]

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

[@mohamadkuk]

Omg this is awesome

[@Henrich205]

Awesome :), thank you :)

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

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

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

[@jaliali84]

Amazing 😍

[@NaveedBarq]

Brilliant

[@GaganDeepSingh-s7r]

How would you calculate the row %change in this pivot table for the distinct count?

[@BenardNjuguna-w9d]

Awsome

[@sikkandhark]

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

[@MIMGURU]

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

[@MrPhanisDav]

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

[@zyngacita]

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

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

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

[@conradoiiaustria5277]

Can we do this then do a running total?

[@jhayg.serrano9349]

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

[@chaddemeuse6760]

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

[@AJIT165]

How can we do both, Distinct count and Sum(for different col's) in single Pivot table.

[@niteshtoshniwal8951]

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

[@adim7909]

Can we use this feature in MacBook ?

[@denisesimmons7754]

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?

[@raunakjain7251]

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

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

[@masterexcel87]

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

[@thabsheerkp]

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.

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