{"id":2751,"date":"2023-01-11T15:39:04","date_gmt":"2023-01-11T15:39:04","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=2751"},"modified":"2024-02-12T15:16:13","modified_gmt":"2024-02-12T15:16:13","slug":"how-to-count-unique-values-and-distinct-values-in-excel","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/how-to-count-unique-values-and-distinct-values-in-excel\/","title":{"rendered":"How to Count Unique Values and Distinct Values in Excel?"},"content":{"rendered":"<p>Many Excel users are quite familiar with the idea of Unique and Distinct values in Microsoft Excel. You may have come across Unique and Distinct values frequently when working on an Excel spreadsheet with a lot of data and numbers. This article will show you how to count Unique and Distinct values in your Excel spreadsheets.<strong>\u00a0<\/strong><\/p>\n<p><strong>This Content Covers:<\/strong><\/p>\n<ol>\n<li><a href=\"#what-is-the-difference-between-unique-value-and-distinct-value?\"><strong> What is the difference between Unique Value and Distinct Value?<\/strong><\/a><\/li>\n<li><a href=\"#how-to-count-unique-values-in-excel?\"><strong> How to Count Unique Values in Excel?<\/strong><\/a>\n<ul>\n<li><strong>\u00a0 \u00a0 Count Unique Values in a Column<\/strong><\/li>\n<li><strong>\u00a0 \u00a0 Count Unique Text Values<\/strong><\/li>\n<li><strong>\u00a0 \u00a0 Count Unique Numbers<\/strong><\/li>\n<li><strong>\u00a0 \u00a0 Count Case-Sensitive Unique Values<\/strong><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#how-to-count-distinct-values-in-excel?\"><strong> How to Count Distinct Values in Excel?<\/strong><\/a>\n<ul>\n<li><strong>\u00a0 \u00a0 Count Distinct Values ignoring Blank Cells<\/strong><\/li>\n<li><strong>\u00a0 \u00a0 Count Distinct Text Values<\/strong><\/li>\n<li><strong>\u00a0 \u00a0 Count Distinct Numbers<\/strong><\/li>\n<li><strong>\u00a0 \u00a0 Method to Count Case-Sensitive Distinct Values<\/strong><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#count-distinct-values-in-excel-pivot-table\"><strong> Count Distinct Values in Excel Pivot Table<\/strong><\/a>\n<ul>\n<li><strong>\u00a0 \u00a0 By Adding a Helper Column in the Dataset<\/strong><\/li>\n<li><strong>\u00a0 \u00a0 By Adding Data to Data Model and Summarizing Using Distinct Count<\/strong><\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<h2><strong id=\"what-is-the-difference-between-unique-value-and-distinct-value?\">1. What is the difference between Unique Value and Distinct Value?<\/strong><\/h2>\n<p>The difference between Unique and Distinct values in Excel is that Unique values are those which are present in our worksheet only once and not more than once,\u00a0but on the other hand, Distinct values can be present in our worksheet multiple times. When counting Excel count distinct values, each data is counted only once regardless of how many times it is present in the datasheet.<\/p>\n<p>For example, have a look at the image below. The Fruit column has a list of names, and Apple, Banana, Jackfruit, and Watermelon are all present just once in the list, making them Unique values in accordance with the discussion we had before. All of the names of these fruits are included in the Distinct column.\u00a0However, names that appear more than once, such as Mango and Guava, are also counted as one.<\/p>\n<p><img fetchpriority=\"high\" decoding=\"async\" class=\"aligncenter size-full wp-image-2467\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-1.png\" alt=\"Count Unique Values and Distinct Values\" width=\"504\" height=\"314\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-1.png 504w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-1-300x187.png 300w\" sizes=\"(max-width: 504px) 100vw, 504px\" \/><\/p>\n<h2><strong id=\"how-to-count-unique-values-in-excel?\">2. How to Count Unique Values in Excel?<\/strong><\/h2>\n<p>There are a good number of ways to count unique Excel values in Microsoft Excel. Some of the most useful methods are shown below.<\/p>\n<h3><strong>2.1 Count Unique Values in a Column<\/strong><\/h3>\n<p>Using the data given in the picture below you must count unique values from the Fruit column.<\/p>\n<p><img decoding=\"async\" class=\"aligncenter size-full wp-image-2468\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-2.png\" alt=\"Count Unique Values and Distinct Values\" width=\"447\" height=\"310\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-2.png 447w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-2-300x208.png 300w\" sizes=\"(max-width: 447px) 100vw, 447px\" \/><\/p>\n<p><strong>Step 1: \u00a0<\/strong>Select cell B2 and type of paste the formula given below in that cell.<\/p>\n<p><strong>=SUM(IF(COUNTIF(A2:A10,A2:A10)=1,1,0))<\/strong><\/p>\n<p><img decoding=\"async\" class=\"aligncenter size-full wp-image-2469\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-3.png\" alt=\"Count Unique Values and Distinct Values\" width=\"638\" height=\"311\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-3.png 638w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-3-300x146.png 300w\" sizes=\"(max-width: 638px) 100vw, 638px\" \/><\/p>\n<p><strong>Step 2: <\/strong>This is an array formula so pressing the Enter key won\u2019t work here. Press <strong>CTRL+SHIFT+ENTER<\/strong> and cell B2 will show how many fruit names are Unique values or how many fruit names are present in the column only once.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2470\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-4.png\" alt=\"Count Unique Values and Distinct Values\" width=\"491\" height=\"301\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-4.png 491w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-4-300x184.png 300w\" sizes=\"(max-width: 491px) 100vw, 491px\" \/><\/p>\n<h3><strong>2.2 Count Unique Text Values<\/strong><\/h3>\n<p>In the picture given below, you have two types of data numeric and text. Follow the steps below to count the Unique Text values from this data.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2471\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-5.png\" alt=\"Count Unique Values and Distinct Values\" width=\"464\" height=\"303\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-5.png 464w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-5-300x196.png 300w\" sizes=\"(max-width: 464px) 100vw, 464px\" \/><\/p>\n<p><strong>Step 1: <\/strong>Input the following formula into cell B2.<\/p>\n<p><strong>=SUM(IF(ISTEXT(A2:A10)*COUNTIF(A2:A10,A2:A10)=1,1,0))<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2472\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-6.png\" alt=\"Count Unique Values and Distinct Values\" width=\"644\" height=\"256\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-6.png 644w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-6-300x119.png 300w\" sizes=\"(max-width: 644px) 100vw, 644px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Press <strong>CTRL+SHIFT+ENTER. <\/strong>In the column Banana, Guava, Mango and Watermelon these 4 are the Text values which are Unique values that cell B2 also confirms after using the formula in it.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2473\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-7.png\" alt=\"Count Unique Values and Distinct Values\" width=\"642\" height=\"294\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-7.png 642w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-7-300x137.png 300w\" sizes=\"(max-width: 642px) 100vw, 642px\" \/><\/p>\n<h3><strong>2.3 Count Unique Numbers<\/strong><\/h3>\n<p>You have two sorts of data\u2014text and numeric\u2014as shown in the image below. To count the Unique Numbers from this data, simply follow the instructions below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2474\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-8.png\" alt=\"Count Unique Values and Distinct Values\" width=\"410\" height=\"307\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-8.png 410w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-8-300x225.png 300w\" sizes=\"(max-width: 410px) 100vw, 410px\" \/><\/p>\n<p><strong>Step 1:<\/strong> Type of paste the formula given below in cell B2 after selecting it.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2475\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-9.png\" alt=\"Count Unique Values and Distinct Values\" width=\"484\" height=\"221\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-9.png 484w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-9-300x137.png 300w\" sizes=\"(max-width: 484px) 100vw, 484px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Use <strong>CTRL+SHIFT+ENTER <\/strong>command and cell B2 will show you the count of Unique Numbers in the worksheet.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2476\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-10.png\" alt=\"Count Unique Values and Distinct Values\" width=\"472\" height=\"208\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-10.png 472w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-10-300x132.png 300w\" sizes=\"(max-width: 472px) 100vw, 472px\" \/><\/p>\n<h3><strong>2.4 Count Case-Sensitive Unique Values<\/strong><\/h3>\n<p>Here an additional Column has been created labeled as Dupe\/Unique as a helper column to identify Unique and Duplicate data. To count Case Sensitive Unique Values using the data of the below given picture, simply follow the steps shown below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2477\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-11.png\" alt=\"Count Unique Values and Distinct Values\" width=\"502\" height=\"300\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-11.png 502w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-11-300x179.png 300w\" sizes=\"(max-width: 502px) 100vw, 502px\" \/><\/p>\n<p><strong>Step 1:<\/strong> Select cell B2 and input the bellow given formula.<\/p>\n<p><strong>=IF(SUM((&#8211;EXACT($A$2:$A$10,A2)))=1,&#8221;Unique&#8221;,&#8221;Dupe&#8221;)<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2478\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-12.png\" alt=\"Count Unique Values and Distinct Values\" width=\"646\" height=\"313\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-12.png 646w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-12-300x145.png 300w\" sizes=\"(max-width: 646px) 100vw, 646px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Click <strong>CTRL+SHIFT+ENTER <\/strong>and cell B2 will show if the data from cell A2 is a Dupe or a Unique data. Now take your mouse cursor to the bottom right corner of cell B2 and drag it down from B2 to B10 to use the same formula in all those cells in order to find out which data is Dupe and which one is Unique.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2479\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-13.png\" alt=\"Count Unique Values and Distinct Values\" width=\"456\" height=\"502\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-13.png 456w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-13-273x300.png 273w\" sizes=\"(max-width: 456px) 100vw, 456px\" \/><\/p>\n<p><strong>Step 3: <\/strong>Select cell C2 and use this formula and click <strong>Enter <\/strong>button cell C2 will show how many Case-Sensitive Unique Values are there in that worksheet.<\/p>\n<p><strong>=COUNTIF(B2:B10, &#8220;Unique&#8221;)<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2480\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-14.png\" alt=\"Count Unique Values and Distinct Values\" width=\"484\" height=\"261\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-14.png 484w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-14-300x162.png 300w\" sizes=\"(max-width: 484px) 100vw, 484px\" \/><\/p>\n<h2><strong id=\"how-to-count-distinct-values-in-excel?\">3. How to Count Distinct Values in Excel?<\/strong><\/h2>\n<h3><strong>3.1 Count Distinct Values ignoring Blank Cells<\/strong><\/h3>\n<p>In the picture given below, there are some blank cells. Now to count Distinct values ignoring blank cells, repeat the steps as shown below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2481\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-15.png\" alt=\"Count Unique Values and Distinct Values\" width=\"351\" height=\"311\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-15.png 351w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-15-300x266.png 300w\" sizes=\"(max-width: 351px) 100vw, 351px\" \/><\/p>\n<p><strong>Step 1: <\/strong>Select cell B2 and input the below given formula in that cell.<\/p>\n<p><strong>=SUM(IF(A2:A10&lt;&gt;&#8221;&#8221;,1\/COUNTIF(A2:A10,A2:A10),0))<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2482\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-16.png\" alt=\"Count Unique Values and Distinct Values\" width=\"612\" height=\"318\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-16.png 612w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-16-300x156.png 300w\" sizes=\"(max-width: 612px) 100vw, 612px\" \/><\/p>\n<p><strong>Step 2: <\/strong>This is an array formula so pressing the Enter key won\u2019t work here also. Press <strong>CTRL+SHIFT+ENTER <\/strong>and cell B2 will show the count of Distinct values ignoring those blank cells.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2483\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-17.png\" alt=\"Count Unique Values and Distinct Values\" width=\"612\" height=\"304\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-17.png 612w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-17-300x149.png 300w\" sizes=\"(max-width: 612px) 100vw, 612px\" \/><\/p>\n<h3><strong>3.2 Count Distinct Text Values<\/strong><\/h3>\n<p>Follow the steps below to count distinct text values from the data of this picture.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2484\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-18.png\" alt=\"Count Unique Values and Distinct Values\" width=\"370\" height=\"325\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-18.png 370w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-18-300x264.png 300w\" sizes=\"(max-width: 370px) 100vw, 370px\" \/><\/p>\n<p><strong>Step 1: <\/strong>Incorporate the formula in Cell B2 after selecting the cell.<\/p>\n<p><strong>=SUM(IF(ISTEXT(A2:A10),1\/COUNTIF(A2:A10, A2:A10),&#8221;&#8221;))<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2485\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-19.png\" alt=\"Count Unique Values and Distinct Values\" width=\"644\" height=\"303\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-19.png 644w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-19-300x141.png 300w\" sizes=\"(max-width: 644px) 100vw, 644px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Press <strong>CTRL+SHIFT+ENTER <\/strong>as this is an array formula and pressing only Enter won\u2019t work correctly.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2486\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-20.png\" alt=\"Count Unique Values and Distinct Values\" width=\"643\" height=\"298\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-20.png 643w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-20-300x139.png 300w\" sizes=\"(max-width: 643px) 100vw, 643px\" \/><\/p>\n<h3><strong>3.3 Count Distinct Numbers<\/strong><\/h3>\n<p>By following the procedure shown below, you can count Distinct numbers from the data of this picture.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2487\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-21.png\" alt=\"Count Unique Values and Distinct Values\" width=\"312\" height=\"300\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-21.png 312w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-21-300x288.png 300w\" sizes=\"(max-width: 312px) 100vw, 312px\" \/><\/p>\n<p><strong>Step 1: <\/strong>Select cell B2 like previous methods and input the formula given below. Now click <strong>CTRL+SHIFT+ENTER<\/strong> key. Cell B2 will display the count of distinct numbers of this datasheet.<\/p>\n<p><strong>=SUM(IF(ISNUMBER(A2:A10),1\/COUNTIF(A2:A10, A2:A10),&#8221;&#8221;))<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2488\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-22.png\" alt=\"Count Unique Values and Distinct Values\" width=\"647\" height=\"273\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-22.png 647w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-22-300x127.png 300w\" sizes=\"(max-width: 647px) 100vw, 647px\" \/><\/p>\n<h3><strong>3.4 Method to Count Case-Sensitive Distinct Values<\/strong><\/h3>\n<p>Here an additional Column has been created labeled as Distinct as a helper column. To count Case Sensitive Distinct Values using the data of the below given picture, simply follow the steps shown below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2489\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-23.png\" alt=\"Count Unique Values and Distinct Values\" width=\"427\" height=\"309\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-23.png 427w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-23-300x217.png 300w\" sizes=\"(max-width: 427px) 100vw, 427px\" \/><\/p>\n<p><strong>Step 1: <\/strong>Select cell B2 and input the bellow given formula. Press <strong>CTRL+SHIFT+ENTER. <\/strong>Take your mouse cursor to bottom right corner of cell B2 and drag it down from B2 to B10.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2490\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-24.png\" alt=\"Count Unique Values and Distinct Values\" width=\"491\" height=\"531\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-24.png 491w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-24-277x300.png 277w\" sizes=\"(max-width: 491px) 100vw, 491px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Select cell C2 and use this formula and click <strong>Enter <\/strong>button and cell C2 will show how many Case-Sensitive Distinct Values are there in that worksheet.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2491\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-25.png\" alt=\"Count Unique Values and Distinct Values\" width=\"454\" height=\"272\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-25.png 454w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-25-300x180.png 300w\" sizes=\"(max-width: 454px) 100vw, 454px\" \/><\/p>\n<h2><strong id=\"count-distinct-values-in-excel-pivot-table\">4. Count Distinct Values in Excel Pivot Table<\/strong><\/h2>\n<h3><strong>4.1 By Adding a Helper Column in the Dataset<\/strong><\/h3>\n<p>You can count Distinct values in Excel Pivot Table by adding a helper column in your datasheet.<\/p>\n<p>Here you have this datasheet as shown in the picture below. Here we will learn how to add a helper column in this datasheet and create a pivot table to count Distinct values.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2492\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-26.png\" alt=\"Count Unique Values and Distinct Values\" width=\"615\" height=\"306\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-26.png 615w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-26-300x149.png 300w\" sizes=\"(max-width: 615px) 100vw, 615px\" \/><\/p>\n<p><strong>Step 1:<\/strong> Column F has been chosen as the helper column in this datasheet. Select cell F2 and insert the formula given below in that cell. And Click enter.<\/p>\n<p><strong>=IF(COUNTIFS($C$2:C2,C2,$B$2:B2,B2)&gt;1,0,1)<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2493\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-27.png\" alt=\"Count Unique Values and Distinct Values\" width=\"645\" height=\"248\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-27.png 645w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-27-300x115.png 300w\" sizes=\"(max-width: 645px) 100vw, 645px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Drag the lower right corner of cell F2 to F10 in order to quickly incorporate the same formula in all these cells.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2494\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-28.png\" alt=\"Count Unique Values and Distinct Values\" width=\"529\" height=\"236\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-28.png 529w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-28-300x134.png 300w\" sizes=\"(max-width: 529px) 100vw, 529px\" \/><\/p>\n<p><strong>Step 3: <\/strong>Select <strong>Pivot Table<\/strong> under <strong>Insert<\/strong> tab.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2495\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-29.png\" alt=\"Count Unique Values and Distinct Values\" width=\"291\" height=\"192\" \/><\/p>\n<p><strong>Step 4:<\/strong> In the Pivot table dialogue box, select the range of your datasheet containing data in the box labeled as <strong>Table\/Range<\/strong>. Then select <strong>New Worksheet<\/strong> and click <strong>OK.<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2496\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-30.png\" alt=\"Count Unique Values and Distinct Values\" width=\"392\" height=\"252\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-30.png 392w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-30-300x193.png 300w\" sizes=\"(max-width: 392px) 100vw, 392px\" \/><\/p>\n<p><strong>Step 5: <\/strong>Drag Region in the Row tab and Count in Values tab. You will have your pivot table which looks like the one below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2497\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-31.png\" alt=\"Count Unique Values and Distinct Values\" width=\"640\" height=\"301\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-31.png 640w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-31-300x141.png 300w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/p>\n<h3><strong>4.2 By Adding Data to Data Model and Summarizing Using Distinct Count<\/strong><\/h3>\n<p>Here you have this datasheet as shown in the picture below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2498\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-32.png\" alt=\"Count Unique Values and Distinct Values\" width=\"563\" height=\"293\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-32.png 563w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-32-300x156.png 300w\" sizes=\"(max-width: 563px) 100vw, 563px\" \/><\/p>\n<p><strong>Step 1: <\/strong>Select <strong>Pivot Table<\/strong> under the <strong>Insert<\/strong> tab. In the Pivot table dialogue box, select the range of your datasheet containing data in the box labeled as <strong>Table\/Range<\/strong>. Then select <strong>New worksheet<\/strong>, check to <strong>Add this data to the Data Model <\/strong>and click <strong>OK.<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2499\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-33.png\" alt=\"Count Unique Values and Distinct Values\" width=\"635\" height=\"200\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-33.png 635w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-33-300x94.png 300w\" sizes=\"(max-width: 635px) 100vw, 635px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Drag Region in Rows section and Sales Rep in Values section. Now you have this pivot table shown below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2500\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-34.png\" alt=\"Count Unique Values and Distinct Values\" width=\"489\" height=\"200\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-34.png 489w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-34-300x123.png 300w\" sizes=\"(max-width: 489px) 100vw, 489px\" \/><\/p>\n<p><strong>Step 3:<\/strong> Right click on any cell containing data under <strong>Count of Sales Rep<\/strong>. Select <strong>Value Field Settings. <\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2501\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-35.png\" alt=\"Count Unique Values and Distinct Values\" width=\"335\" height=\"306\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-35.png 335w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-35-300x274.png 300w\" sizes=\"(max-width: 335px) 100vw, 335px\" \/><\/p>\n<p><strong>Step 4:<\/strong> From <strong>Value Field Settings <\/strong>dialogue box select Distinct Count and click <strong>OK.<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2502\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-36.png\" alt=\"Count Unique Values and Distinct Values\" width=\"390\" height=\"340\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-36.png 390w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-36-300x262.png 300w\" sizes=\"(max-width: 390px) 100vw, 390px\" \/><\/p>\n<p><strong>Step 5: <\/strong>In the pivot table the column name changed from Count of Sales Rep to Distinct Count of Sales Rep.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-2503\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-37.png\" alt=\"Count Unique Values and Distinct Values\" width=\"389\" height=\"275\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-37.png 389w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/01\/Count-Unique-Values-and-Distinct-Values-37-300x212.png 300w\" sizes=\"(max-width: 389px) 100vw, 389px\" \/><\/p>\n<p>&nbsp;<\/p>\n<div class=\"elementor-element elementor-element-3db31e1 elementor-widget elementor-widget-theme-post-content\" data-id=\"3db31e1\" data-element_type=\"widget\" data-widget_type=\"theme-post-content.default\">\n<div class=\"elementor-widget-container\">\n<p>You may be interested:<\/p>\n<ol>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Finance\" target=\"_blank\" rel=\"noopener\">Financial Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Sales\" target=\"_blank\" rel=\"noopener\">Sales Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Human-Resource\" target=\"_blank\" rel=\"noopener\">HR Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.executiveknowledge.org\/dashboard-templates\/35\" target=\"_blank\" rel=\"noopener\">Data Visualization Charts<\/a><\/li>\n<\/ol>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Many Excel users are quite familiar with the idea of Unique and Distinct values in Microsoft Excel. You may have come across Unique and Distinct values frequently when working on an Excel spreadsheet with a lot of data and numbers. This article will show you how to count Unique and Distinct values in your Excel &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"https:\/\/www.bizinfograph.com\/resource\/how-to-count-unique-values-and-distinct-values-in-excel\/\"> <span class=\"screen-reader-text\">How to Count Unique Values and Distinct Values in Excel?<\/span> Read More &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","footnotes":""},"categories":[345],"tags":[347,346,348],"class_list":["post-2751","post","type-post","status-publish","format-standard","hentry","category-count-unique-values","tag-count-unique-values","tag-count-unique-values-and-distinct-values-in-excel","tag-distinct-values-in-excel"],"_links":{"self":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/2751","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/comments?post=2751"}],"version-history":[{"count":7,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/2751\/revisions"}],"predecessor-version":[{"id":8620,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/2751\/revisions\/8620"}],"wp:attachment":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/media?parent=2751"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/categories?post=2751"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/tags?post=2751"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}