Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Counting Distinct Unique data using Pivot Table

Counting Distinct Unique data using Pivot Table

In this 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'.

count the distinct unique data in a particular column

Display and the 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.
Count a unique data using Excel formula

In the picture 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 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.

Count distinct unique data in the column regardless of the number of occurrences
PivotTable1 and the PivotTable3 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 startup window.

Creating pivot table and the the counting of distinct unique data
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.

Adding and the arranging items in a Pivot Table field

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".

Distinct Count of Name in Pivot Table

Click on the 'OK' button and now you have shown Pivot Table, arrange the items in the Pivot Table as shown below.

Change the function of the pivotal table

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.

Count unique records or data in a filtered Excel range
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))

NOTE!
ARRAY or CSE formula should be finished by pressing Ctrl+Shift+Enter (not just enter)

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