MrExcel Publishing
Your One Stop for Excel Tips & Solutions
Microsoft MVP

Excel 2019: Count Distinct

June 17, 2019 - by Bill Jelen

Count Distinct. Photo Credit: Monika Grabkowska at Unsplash.com

To see an annoyance with pivot tables, drag the Customer column from the Data table to the VALUES area. The field says Count of Customer, but it is really a count of the invoices belong to each sector. What if you really want to see how many unique customers belong to each sector?

This report showing Count of Customer by Sector is really giving you the number of orders in each sector.

The trick is to build your pivot table using the Data Model. Double-click the Count of Customer heading. At first, the Summarize Values By offers choices such as Sum, Average, and Count. Scroll down to the bottom. Because the pivot table is based on the Data Model, you now have Distinct Count.

continue reading »

More Reading

Eliminate VLOOKUP with the Data Model. Photo Credit: Fredy Jacob at Unsplash.com

Excel 2019: Eliminate VLOOKUP with the Data Model »

June 13, 2019 - by Bill Jelen

Say that you have a data set with product, date, customer, and sales information. The IT department forgot to put sector in there. Here is a lookup table that maps customer to sector.

Excel See Why GETPIVOTDATA Might Not Be Entirely Evil. Photo Credit: Manki Kim at Unsplash.com

Excel 2019: See Why GETPIVOTDATA Might Not Be Entirely Evil »

June 12, 2019 - by Bill Jelen

Most people first encounter GETPIVOTDATA when they try to build a formula outside a pivot table that uses numbers in the pivot table. For example, this variance percentage won’t copy down to the other months due to Excel inserting GETPIVOTDATA functions.

read more articles »

IMA Data Analytics Leveraging Excel

Featured Products

Your Excel Survival Kit

Your Excel Survival Kit »

This book helps you realize that Excel is on your side. It can be your friend, not your enemy. If and when you upgrade your Excel skills you will experience many positives: you will learn to love Excel, you will learn how to use Excel to become an invaluable asset in your workplace.

Cleaning Excel Data With Power Query Straight To The Point

Cleaning Excel Data With Power Query Straight To The Point »

This book provides a quick Straight-to-the-Point introduction to Power Query. There’s a lot here, and there’s also a lot that’s not here. Reading this book will be like sitting down at a café and striking up a conversation with the person at the next table, where you learn enough random stuff about that person to decide if you want to know more and keep in touch.

Power Excel With MrExcel - 2017 Edition

Power Excel With MrExcel - 2017 Edition »

This is the print book edition of "Power Excel with MrExcel - 2017 Edition" - by Bill Jelen. Master Pivot Tables, Subtotals, Visualizations, VLOOKUP, Power BI and Data Analysis.

Microsoft Excel 2019 Pivot Table Data Crunching

Microsoft Excel 2019 Pivot Table Data Crunching »

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.

The Absolute Guide to Dashboarding & Reporting with Power BI

The Absolute Guide to Dashboarding & Reporting with Power BI »

Learn Power BI Dashboarding Techniques by Watching A Pro!

Microsoft Excel 2019 Inside Out

Microsoft Excel 2019 Inside Out »

Dive into Microsoft Excel 2019–and really put your spreadsheet expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds–all you need to make the most of Excel’s most powerful tools for analyzing data and making better decisions.

see more products »