Counting Distinct Unique Data Using Pivot Table or Formula
Count and Display Unique Data or Values
In this Excel tutorial I will show how we can Count and the Display Unique Data or Values. In the picture below you see the range of cells that contains the data for this tutorial. Our goal is to display and the to count how many unique names in column 'B', also our goal is to display and the to count how many unique product name in column 'C'.
Counting of Unique Data Using the Formula
If you want to display and the count all unique data appearing in a column, then you can use the following formula as shown below. So you notice in the column 'M', we have displayed Unique Names that appear in the column 'B', regardless of whether they appear one or more times. You also note that in the column 'N' we have displayed data in column 'C' who that appear on once or more times. In the cells 'M7' and the 'N7' is a formula that counts the number of data above.
In the image above formula are as follows
ARRAY formula in cell 'M2' is as follows (copy the formula down so after the right)
=IF(IFERROR(INDEX(B$2:B$12;MATCH(0;COUNTIF(M$1:M1;B$2:B$12);0));0)=0;"";IFERROR(INDEX(B$2:B$12;MATCH(0;COUNTIF(M$1:M1;B$2:B$12);0));0))
The formula in cell 'M7' is as follows (copy the formula to the right)
=COUNTA(M2:M6)-COUNTBLANK(M2:M6)
Counting Unique Data Using Pivot Table
If we want to Count the Unique Data, we can create and insert a Pivot Table. How to create a pivot table refer to the respective link. Using the Pivot Table we can have a look at the Unique Data related to the second column and the so allocate a unique combination of data. In the picture below you see created four Pivot Table.
PivotTable1 and the PivotTable3 on image above displayed standard counting Data in the 'C' column. So for each, we can see how many times it appears in the column, regardless of whether it appears once or more times.
PivotTable2 and the PivotTable4 displayed non-standard counting Data in the 'B' column using the function or command "Add this data to the Data Model" (This command is available in Excel 2013). So for each, we can see how many times it appears in the column provided you do not count the repetition of data.
To create an pivot table PivotTable2 and the PivotTable4 do the following.
When creating a Pivot Table turn on "Add this data to the Data Model" on the first start window.
After clicking the 'OK' button appears the new frame in the right side of the Excel window titled "PivotTable Fields". Arrange desired items in rows and columns, so now you have a situation like in the picture below.
In the window "PivotTable Fields >> VALUES", you need to click on 'Count of Name' and after on the pop-up menu, click on "Value Field Settings", and then in the dialog box, select "Distinct Count".
Click on the 'OK' button and now you have shown Pivot Table, arrange the items in the Pivot Table as shown below.
Count Unique Data from Filtered Range
If you want to count how many Unique Data in a column after filtering table, then use one of the ways and that is the formula below.
ARRAY formula in cell 'F14' is as follows
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3;OFFSET(B2:B14;ROW(B2:B14)-ROW(B2);0;1));MATCH(B2:B14;B2:B14;0));ROW(B2:B14)-ROW(B2)+1);1))
ARRAY formula in cell 'H14' is as follows
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3;OFFSET(C2:C14;ROW(C2:C14)-ROW(C2);0;1));MATCH(C2:C14;C2:C14;0));ROW(C2:C14)-ROW(C2)+1);1))
This topic is related to the tutorial
BTW: Pay attention, you may need as a separator of arguments formula use a comma (,) instead of a semicolon (; )
Copy formula to Notepad and with Find/Replace change semicolon to comma.