{"id":424823,"date":"2022-03-31T18:00:28","date_gmt":"2022-03-31T15:00:28","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-count-unique-values-in-microsoft-excel\/"},"modified":"2022-03-31T18:00:28","modified_gmt":"2022-03-31T15:00:28","slug":"how-to-count-unique-values-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-count-unique-values-in-microsoft-excel\/","title":{"rendered":"#How to Count Unique Values in Microsoft Excel"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_85 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a412b21c7940\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a412b21c7940\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-count-unique-values-in-microsoft-excel\/#%E2%80%9CHow_to_Count_Unique_Values_in_Microsoft_Excel%E2%80%9D\" >&#8220;How to Count Unique Values in Microsoft Excel&#8221;<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-count-unique-values-in-microsoft-excel\/#Use_the_COUNTA_and_UNIQUE_Functions\" >Use the COUNTA and UNIQUE Functions<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-count-unique-values-in-microsoft-excel\/#Use_the_SUM_and_COUNTIF_Functions\" >Use the SUM and COUNTIF Functions<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"%E2%80%9CHow_to_Count_Unique_Values_in_Microsoft_Excel%E2%80%9D\"><\/span>&#8220;How to Count Unique Values in Microsoft Excel&#8221;<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-757715\" data-pagespeed-lazy-srcset=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=398&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 400w, https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 1200w\" sizes=\"auto, 400w, 1200w\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Microsoft Excel logo on a green background\" width=\"1200\" height=\"675\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You can use the COUNT function and variations to count cells that contain numbers. But what if you want to count only the distinct values in a cell range? We\u2019ll show you two ways to count unique values in Excel.<\/p>\n<p>Both methods we\u2019ll explain use a combination of functions and variations of the COUNT function. And because your cell range could include blank cells, we\u2019re including alternative formulas to account for this.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Use the COUNT Function in Microsoft Excel<\/em><\/strong><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Use_the_COUNTA_and_UNIQUE_Functions\"><\/span>Use the COUNTA and UNIQUE Functions<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>This first method is for those who use Excel for <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/669400\/what-is-microsoft-365\/\">Microsoft 365, Excel for the web, Excel 2021, or Excel for iPhone, iPad, or Android phones or tablets. This is because the UNIQUE function is only available in these and later versions of Excel.<\/p>\n<p>The UNIQUE function provides all distinct values in a cell range. Because you don\u2019t want to list those unique values, but count them instead, you\u2019ll add the COUNTA function. The COUNTA function counts nonblank cells.<\/p>\n<p>To list the unique values in the cell range A2 through A5, you would use this formula:<\/p>\n<pre>=UNIQUE(A2:A5)<\/pre>\n<p>You can see here we have three distinct values listed.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-792418\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/UniqueFunction-ExcelCountUnique.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"UNIQUE function in Excel\" width=\"480\" height=\"220\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>To count those unique values instead of listing them, you add the COUNTA function to the beginning of the formula:<\/p>\n<pre>=COUNTA(UNIQUE(A2:A5))<\/pre>\n<p>Now you have the count for those unique values which is 3.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-792417\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/CountaUniqueFunctions-ExcelCountUnique.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"COUNTA with UNIQUE in Excel\" width=\"540\" height=\"220\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>If the cell range you want to count contains blanks, those will be included as unique values which may cause a problem. To exclude blank cells in your range, you can add the FILTER function to the formula:<\/p>\n<blockquote class=\"admonishment_note\"><p><strong>Note:<\/strong> The FILTER function is only available in the versions of Excel listed <a rel=\"nofollow noopener\" target=\"_blank\" href=\"#moka_anchor_Versions\">above<\/a>.<\/p><\/blockquote>\n<pre>=COUNTA(UNIQUE(FILTER(A2:A5,A2:A5&lt;&gt;\"\")))<\/pre>\n<p>Let\u2019s break down the FILTER portion of the formula. <code>A2:A5,A2:A5<\/code> represents the cell range and the criteria to filter which are the same, <code>&lt;&gt;<\/code> represents not equal to, and <code>\"\"<\/code> represents blank.<\/p>\n<p>As you can see in the screenshot below, the blank cell in our array is not counted as a unique value.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-792416\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/CountaUniqueFilterFunctions-ExcelCountUnique.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"COUNTA, UNIQUE, and FILTER functions\" width=\"650\" height=\"210\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Count Cells in Microsoft Excel<\/em><\/strong><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Use_the_SUM_and_COUNTIF_Functions\"><\/span>Use the SUM and COUNTIF Functions<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>If you\u2019re using a version of Excel where the UNIQUE and FILTER functions are unavailable, you can use SUM and COUNTIF instead.<\/p>\n<blockquote class=\"admonishment_note\"><p><strong>Note:<\/strong> You may also use the same formulas below replacing the SUM function with SUMPRODUCT.<\/p><\/blockquote>\n<p>The SUM function adds numbers and the COUNTIF function counts those cells containing numbers that meet specific criteria.<\/p>\n<p>To find the unique values in the cell range A2 through A5, use the following formula:<\/p>\n<pre>=SUM(1\/COUNTIF(A2:A5,A2:A5))<\/pre>\n<p>To break down this formula, the <code>COUNTIF<\/code> function counts the cells with numbers in our range and uses that same cell range as the criteria. That result then is divided by <code>1<\/code> and the <code>SUM<\/code> function adds the remaining values.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-792415\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/SumCountifFunctions-ExcelCountUnique.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"SUM and COUNTIF functions in Excel\" width=\"595\" height=\"220\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Similar to the first method, you can run into issues if you have blanks in your cell range with this method. Only, you\u2019ll receive the #DIV\/0! error rather than an extra unique value.<\/p>\n<p>To eliminate this problem, you can use the following formula:<\/p>\n<pre>=SUM((A2:A5&lt;&gt;\"\")\/COUNTIF(A2:A5,A2:A5&amp;\"\"))<\/pre>\n<p>The additional portion for the <code>COUNTIF<\/code> function concatenates a blank string to prevent zeroes in the results\u00a0because you cannot divide by zero. The additional portion for the <code>SUM<\/code> function adds the values that do not equal blank. All of this provides the count of distinct values and also does not count blanks as unique.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-792413\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/SumCountifFunctionsFiltered-ExcelCountUnique.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"SUM and COUNTIF filtered\" width=\"650\" height=\"210\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Finding unique values in Excel doesn\u2019t have to be a difficult task. By using the functions and formulas here, you should see those distinct values in no time!<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>Functions vs. Formulas in Microsoft Excel: What&#8217;s the Difference?<\/em><\/strong><\/p>\n<\/div>\n<p><script>\n setTimeout(function(){\n  !function(f,b,e,v,n,t,s)\n  {if(f.fbq)return;n=f.fbq=function(){n.callMethod?\n  n.callMethod.apply(n,arguments):n.queue.push(arguments)};\n  if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version='2.0';\n  n.queue=[];t=b.createElement(e);t.async=!0;\n  t.src=v;s=b.getElementsByTagName(e)[0];\n  s.parentNode.insertBefore(t,s) } (window, document,'script',\n  'https:\/\/connect.facebook.net\/en_US\/fbevents.js');\n   fbq('init', '335401813750447');\n   fbq('track', 'PageView');\n  },3000);\n<\/script><\/p>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.howtogeek.com\/792407\/count-unique-values-in-excel\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;How to Count Unique Values in Microsoft Excel&#8221; You can use the COUNT function and variations to count cells that contain numbers. But what if you want to count only the distinct values in a cell range? We\u2019ll show you two ways to count unique values in Excel. Both methods we\u2019ll explain use a combination&#8230;<\/p>\n","protected":false},"author":1,"featured_media":424824,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-424823","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/424823","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=424823"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/424823\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/424824"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=424823"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=424823"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=424823"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}