Use Distinct Count In Excel

Apr 19, 2021 There are a couple of ways to get distinct values in Excel. In this article, I will show you 6 different ways. These ways allow you to deal with almost all kinds of situation when it comes to getting unique values. In this article, we will be using the following list as an example. Details: Count unique values in Excel You can use the combination of the SUM and COUNTIF functions to count unique values in Excel. The syntax for this combined formula is = SUM (IF (1/COUNTIF (data, data)=1,1,0)). Here the COUNTIF formula counts the number of times each value in the range appears. Excel formula distinct count. Excel Formula Training. Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges.

Distinct Count In Excel Pivot
Distinct Count In Excel Pivot Table
How To Use Distinct Count In Excel
Use Distinct Count In Excel Function
Distinct Count In Excel Pivot Table
Use Distinct Count In Excel 2013

When you create a pivot table to summarize data, Excel automatically creates sums and counts for the fields that you add to the Values area. In addition, you might want to see a distinct count (unique count) for some fields, such as:

The number of distinct salespeople who made sales in each region
The count of unique products that were sold in each store

Normal Pivot Tables

For a normal pivot table, there isn’t a built-in distinct count feature in a normal pivot table. However, in Excel 2013 and later versions, you can use a simple trick, described below, to show a distinct count for a field.

Ldplayer 4 for mac. For older versions of Excel, try one of the following methods:

In Excel 2010, use a technique to “Pivot the Pivot table”.
In Excel 2007 and earlier versions, add a new column to the source data, and Use CountIf.

Add to Data Model – Excel 2013 and Later

In Excel 2013, if you add a pivot table’s source data to the workbook’s Data Model, it is easy to create a distinct count in Excel pivot table.

NOTE: This technique creates an OLAP-based pivot table, which has some limitations, such as no ability to add calculated fields or calculated items. If you need the restricted features, try the “Pivot the Pivot” method instead.

The Sample Data

In this example, there are 4999 records that show product sales, with the region and salesperson name. The first few records are shown in the screen shot below.

You can download the sample workbook from my Contextures website. On the Pivot Table Unique Count page, go to the Download section, and click the link.

Create the Pivot Table

First, to create a pivot table that will show a distinct count, follow these steps:

Select a cell in the source data table.
At the bottom of the Create PivotTable dialog box, add a check mark to “Add this data to the Data Model”
Click OK

Set up the Pivot Table Layout

Distinct Count In Excel Pivot

To set up the pivot table layout, follow these steps:

In the pivot table, add Region to the Row area.
Add these 3 fields to the Values area — Person, Units, Value
The Person field contains text, so it defaults to Count of Person. The count shows the total number of transactions in each region, not a unique count of salespeople

Show a Distinct Count

To get a unique count (distinct count) of salespeople in each region, follow these steps:

Right-click one of the values in the Person field
Click Value Field Settings

In the Summarize Value Field By list, scroll to the bottom, and click Distinct Count, then click OK

The Person field changes, and instead of showing the total count of transactions, it shows a distinct count of salespeople names.

Distinct Count in Excel Pivot Table Workbook

To download the sample workbook, go to the Pivot Table Unique Count page, on my Contextures website. On that page, go to the Download section, and click the link.

Distinct Count In Excel Pivot Table

How To Use Distinct Count In Excel

That page also has instructions for calculating a unique count in older versions of Excel.

_________________________

Save

Counting is the poor cousin to summing in Excel. Not many people count things, but everyone adds up things. There is a special sort of count that can be useful. A distinct count counts unique entries and is hard to do with a formula. If you have Excel 2013 or a later version you can use a PivotTable to perform a distinct count.

Distinct counts are usually a data base calculation. As you will see, for reporting purposes, Excel is becoming more like a data base.

The data table below has 10 rows and the standard PivotTable report counts 10 invoices. There are only 5 invoice numbers. You can download the file at the button at the bottom of the post.

If we want to count the unique invoice numbers we need to change a couple of settings in the Create PivotTable dialog. This is only possible in Excel 2013 and later versions. If you are using Excel 2010 then you need to use PowerPivot.

When you insert the PivotTable you need to initially instruct Excel to add the table to what is called the data model. The data model allows you to treat Excel like a relational database. You need to tick the box at the bottom of the Create PivotTable dialog to add the table to the data model – see image below.

After you drag the invoice number to the values section you can change the calculation by using the Value Field Settings option – see image below.

At the bottom of the list is the Distinct Count option – see image below.

Multi instance bluestacks mac. As you can see from the new report below, there are five unique invoice numbers.

Be aware that when you are using distinct count the numbers are unlikely to add up. The new report shows that there were four invoices that included a gadget and two invoices with a widget. Reinstall os x el capitan.