Distinct Count in Excel

The Data Model to the rescue

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.

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.

From the data table we can see there were three invoices that just had a gadget. One invoice just had a widget and one invoice had a widget and a gadget.

After you have added a table to the data model the future Create PivotTable dialogs will allow you to use the data model option in the middle of the dialog – see image below.

This option was greyed out in the previous Create PivotTable dialog. The bottom option is now greyed out because the table is already is in the data model.

Download Example File

Please note: I reserve the right to delete comments that are offensive or off-topic.