{"id":437490,"date":"2022-04-25T21:00:22","date_gmt":"2022-04-25T18:00:22","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-count-unique-values-in-google-sheets\/"},"modified":"2022-04-25T21:00:22","modified_gmt":"2022-04-25T18:00:22","slug":"how-to-count-unique-values-in-google-sheets","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-count-unique-values-in-google-sheets\/","title":{"rendered":"#How to Count Unique Values in Google Sheets"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_84 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-6a295dd80cbae\" 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-6a295dd80cbae\" 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-google-sheets\/#%E2%80%9CHow_to_Count_Unique_Values_in_Google_Sheets%E2%80%9D\" >&#8220;How to Count Unique Values in Google Sheets&#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-google-sheets\/#Use_the_COUNTUNIQUE_Function_in_Google_Sheets\" >Use the COUNTUNIQUE Function in Google Sheets<\/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-google-sheets\/#Use_the_COUNTUNIQUEIFS_Function_for_Adding_Criteria\" >Use the COUNTUNIQUEIFS Function for Adding Criteria<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"%E2%80%9CHow_to_Count_Unique_Values_in_Google_Sheets%E2%80%9D\"><\/span>&#8220;How to Count Unique Values in Google Sheets&#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-731741\" data-pagespeed-lazy-srcset=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_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\/05\/google_sheets_her_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\/05\/google_sheets_her_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"\" 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>Counting the number of distinct values in a spreadsheet is valuable in many situations. Whether it\u2019s customer names, product numbers, or dates, one simple function can help you count unique values in Google Sheets.<\/p>\n<p>Unlike Microsoft Excel which has various ways to count distinct values\u00a0depending on your version of Excel, Google Sheets offers a handy function that uses a basic formula. Luckily, the function works with numbers, text, cell references, inserted values, and combinations of all for complete flexibility.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Use_the_COUNTUNIQUE_Function_in_Google_Sheets\"><\/span>Use the COUNTUNIQUE Function in Google Sheets<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>COUNTUNIQUE is one of those Google Sheets functions that you\u2019ll <a href=\"https:\/\/buradabiliyorum.com\/en\/category\/download-scripts-themes-apps\/\" data-internallinksmanager029f6b8e52c=\"9\" title=\"Download Scripts &amp; Themes &amp; Apps\" target=\"_blank\" rel=\"noopener\">app<\/a>reciate once you start using it. Save time and manual work counting cells that are unlike the rest.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>9 Basic Google Sheets Functions You Should Know<\/em><\/strong><\/p>\n<p>The syntax is <code>COUNTUNIQUE(value1, value2, ...)<\/code> where only the first argument is required. Let\u2019s look at a handful of examples so that you can use the function effectively for different types of data.<\/p>\n<p>To count the number of unique values in the cell range A1 through A16, you would use the following formula:<\/p>\n<pre>=COUNTUNIQUE(A1:A16)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-795238\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/Range-GoogleSheetsCountUnique.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"COUNTUNIQUE for a range\" width=\"500\" height=\"425\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Maybe you have your own values that would want to insert rather than those displayed within cells. With this formula, you can count the number of unique values you insert:<\/p>\n<pre>=COUNTUNIQUE(1,2,3,2,3,4)<\/pre>\n<p>Here, the result is 4 because values 1, 2, 3, and 4 are unique regardless of how many times they appear.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-795239\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/Value-GoogleSheetsCountUnique.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"COUNTUNIQUE for inserted values\" width=\"350\" height=\"112\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>For this next example, you can count inserted values as shown above combined with values in a cell range. You\u2019d use this formula:<\/p>\n<pre>=COUNTUNIQUE(1,2,3,A2:A3)<\/pre>\n<p>In this case, the result is 5. The numbers 1, 2, and 3 are unique as are the values within the cell range A2 through A3.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-795240\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/ValueRange-GoogleSheetsCountUnique.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"COUNTUNIQUE for a range and inserted values\" width=\"600\" height=\"135\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>If you want to include words as inserted values, the function counts these as unique elements as well. Look at this formula:<\/p>\n<pre>=COUNTUNIQUE(1,2,3,\"word\",4)<\/pre>\n<p>The result is 5 because each value in the formula is distinct, whether it\u2019s a number or text.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-795241\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/ValueText-GoogleSheetsCountUnique.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"COUNTUNIQUE for inserted values and text\" width=\"366\" height=\"100\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>For the ultimate combination, you can use a formula like this to count inserted values, text, and a cell range:<\/p>\n<pre>=COUNTUNIQUE(1,2,3,\"word\",A2:A3)<\/pre>\n<p>The result here is 6 which counts the numbers 1, 2, and 3, the text, and the unique values in the range A2 through A3.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-795242\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/ValueTextRange-GoogleSheetsCountUnique.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"COUNTUNIQUE for a range, inserted values, and text\" width=\"620\" height=\"135\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Use_the_COUNTUNIQUEIFS_Function_for_Adding_Criteria\"><\/span>Use the COUNTUNIQUEIFS Function for Adding Criteria<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Not everything in a spreadsheet is simple. If you like the idea of the COUNTUNIQUE function but would like to count unique values based on criteria, you can use COUNTUNIQUEIFS. The nice thing about this function is that you can use one or more sets of ranges and conditions.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Count Data Matching Set Criteria in Google Sheets<\/em><\/strong><\/p>\n<p>The syntax is <code>COUNTUNIQUEIFS(count_range, criteria_range1, criteria, criteria_range2, criteria2, ...)<\/code> where the first three arguments are required.<\/p>\n<p>In this first example, we want to count the unique values in the range A2 through A6 where the value in the range F2 through F6 is greater than 20. Here\u2019s the formula:<\/p>\n<pre>=COUNTUNIQUEIFS(A2:A6,F2:F6,\"&gt;20\")<\/pre>\n<p>The result here is 2. Even though there are three values greater than 20 in the F2 through F6 range, the function provides only those that are unique from the range A2 through A6, being Wilma Flintstone and Bruce Banner. The latter appears twice.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-795244\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/IfGreaterThan-GoogleSheetsCountUnique.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"COUNTUNIQUEIFS for greater than a value\" width=\"532\" height=\"215\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Let\u2019s use this function with text criteria. To count the unique values in the range A2 through A6 where the text in the range E2 through E6 equals Delivered, you\u2019d use this formula:<\/p>\n<pre>=COUNTUNIQUEIFS(A2:A6,E2:E6,\"Delivered\")<\/pre>\n<p>In this case, the result is also 2. Although we have three marked as Delivered, only two names in our A2 through A6 range are unique, Marge Simpson and Bruce Banner. Again, Bruce Banner appears twice.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-795243\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/IfText-GoogleSheetsCountUnique.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"COUNTUNIQUEIFS for text\" width=\"455\" height=\"205\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Like counting the number of unique values in your spreadsheet, highlighting duplicates in Google Sheets or removing duplicates completely is also helpful.<\/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\/795235\/how-to-count-unique-values-in-google-sheets\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;How to Count Unique Values in Google Sheets&#8221; Counting the number of distinct values in a spreadsheet is valuable in many situations. Whether it\u2019s customer names, product numbers, or dates, one simple function can help you count unique values in Google Sheets. Unlike Microsoft Excel which has various ways to count distinct values\u00a0depending on your&#8230;<\/p>\n","protected":false},"author":1,"featured_media":437491,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/google_sheets_her_1200x675.jpg?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-437490","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\/437490","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=437490"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/437490\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/437491"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=437490"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=437490"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=437490"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}