It is now easier to create a distinct count formula in the subscription version of Excel. You can also use a criteria. A distinct count only counts each value once. Duplicate entries are ignored.
In the table below the invoice number is repeated. You can download the file using the button at the bottom of the post.
If we want to count the number of invoices in the table we could use a Pivot Table and I did a post about that here.
To find out how many invoices are in the table we can use a new function with an old function.
New Function
The UNIQUE function was added in 2020 to the subscription version of Excel. It returns the unique entries from a range. This I part of the dynamic array functionality that was also added in 2020.
The UNIQUE function will spill down to list the entries – see the image below.
I have covered the UNIQUE function in this blog post.
The formula in cell H2 is.
=UNIQUE(tblSales[Invoice])
This is using the structured references from the formatted table called tblSales. Structured references are table-based names. The Format as Table icon on the Home ribbon creates these special tables.
You could have also used.
=UNIQUE(B2:B11)
We want to count the entries, so we can use the COUNTA function around the UNIQUE – see image below.
The COUNTA function counts all entries. There are 5 unique invoice numbers in the table. The final formula in cell H2 is.
=COUNTA(UNIQUE(tblSales[Invoice]))
or
=COUNTA(UNIQUE(B2:B11))
Note: the COUNT function only counts numbers and dates. COUNTA counts everything.
Using a Criteria
Let’s say we want to count the number of invoices that included a Gadget. We can use another new function to filter the Invoice column based on the Product column.
The FILTER function – see in the image below – allows you to create a list based on applying a filter, in this case Gadget in the Product column.
The formula in cell H4 is.
=FILTER(tblSales[Invoice],tblSales[Product]=G4)
or
=FILTER($B$2:$B$11,$C$2:$C$11=G4)
This lists all the invoices (including duplicates where the product is a Gadget (cell G4).
We can wrap the UNIQUE function around the FILTER function to get the unique invoice numbers – see image below.
The formula in cell H4 now is.
=UNIQUE(FILTER(tblSales[Invoice],tblSales[Product]=G4))
or
=UNIQUE(FILTER($B$2:$B$11,$C$2:$C$11=G4))
We can then wrap the COUNTA function around that result to count the invoices.
The final formula for cell H4 is.
=COUNTA(UNIQUE(FILTER(tblSales[Invoice],tblSales[Product]=G4)))
or
=COUNTA(UNIQUE(FILTER($B$2:$B$11,$C$2:$C$11=G4)))
This counts the number of invoices that sold a gadget. We can copy the formula down to handle Widgets – see below.
Note: the total number of invoices doesn’t “Add Up” because one invoice 1237 included both a Widget and a Gadget and so it is included in both Gadget and Widget counts.
Please note: I reserve the right to delete comments that are offensive or off-topic.