{"id":353623,"date":"2021-10-15T18:00:54","date_gmt":"2021-10-15T15:00:54","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-use-the-countif-formula-in-microsoft-excel\/"},"modified":"2021-10-15T18:00:54","modified_gmt":"2021-10-15T15:00:54","slug":"how-to-use-the-countif-formula-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-use-the-countif-formula-in-microsoft-excel\/","title":{"rendered":"#How to Use the COUNTIF Formula in Microsoft Excel"},"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-6a2933d25fba6\" 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-6a2933d25fba6\" 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-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-the-countif-formula-in-microsoft-excel\/#What_Is_the_COUNTIF_function\" >What Is the COUNTIF function?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-the-countif-formula-in-microsoft-excel\/#How_to_Use_the_COUNTIF_Formula_in_Microsoft_Excel\" >How to Use the COUNTIF Formula in Microsoft Excel<\/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-use-the-countif-formula-in-microsoft-excel\/#How_to_Count_the_Number_of_Multiple_Values\" >How to Count the Number of Multiple Values<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-the-countif-formula-in-microsoft-excel\/#Limitations_of_the_COUNTIF_Formula\" >Limitations of the COUNTIF Formula<\/a><\/li><\/ul><\/nav><\/div>\n<p><strong>&#8220;#How to Use the COUNTIF Formula in Microsoft Excel&#8221;<\/strong><\/p>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-757715\" 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\" 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\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>In Microsoft Excel, <code>COUNTIF<\/code> is one of the most widely used formulas. It counts all cells in a range that matches a single condition or multiple conditions, and it\u2019s equally useful in counting cells with numbers and text in them.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"What_Is_the_COUNTIF_function\"><\/span>What Is the COUNTIF function?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><code>COUNTIF<\/code> allows users to count the number of cells that meet certain criteria, such as the number of times a part of a word or specific words <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>ears on a list. In the actual formula, you\u2019ll tell Excel where it needs to look and what it needs to look for. It counts cells in a range that meets single or multiple conditions, as we\u2019ll demonstrate below.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"How_to_Use_the_COUNTIF_Formula_in_Microsoft_Excel\"><\/span>How to Use the COUNTIF Formula in Microsoft Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>For this tutorial, we will use simple two-column inventory chart logging school supplies and their quantities.<\/p>\n<p>In an empty cell, type <code>=COUNTIF<\/code>\u00a0 followed by an open bracket. The first argument \u201crange\u201d asks for the range of cells you would like to check. The second argument \u201ccriteria\u201d asks for what exactly you want Excel to count. This is usually a text string. So, in double-quotes, add the string you want to find. Be sure to add the closing quotemark and the closing bracket.<\/p>\n<p>So in our example, we want to count the number of times \u201cPens\u201d appears in our inventory, which includes the range <code>G9:G15<\/code> . We\u2019ll use the following formula.<\/p>\n<pre>=COUNTIF(G9:G15,\"Pens\")<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-753619 size-full\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/countif-formula-e1633356779927.jpg?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"countif formula example\" width=\"327\" height=\"190\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You can also count the number of times a specific number appears by putting the number in the criteria argument without quotes. Or you can use operators with numbers inside of quotes to determine results, like <code>\"&lt;100\"<\/code>\u00a0to get a count of all numbers less than 100.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Count Colored Cells in Microsoft Excel<\/em><\/strong><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"How_to_Count_the_Number_of_Multiple_Values\"><\/span>How to Count the Number of Multiple Values<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To count the number of multiple values (e.g. the total of pens and erasers in our inventory chart), you may use the following formula.<\/p>\n<pre>=COUNTIF(G9:G15, \"Pens\")+COUNTIF(G9:G15, \"Erasers\")<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-753618 size-full\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/08\/countif-multiple-e1633356801846.jpg?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"countif multiple example formula\" width=\"383\" height=\"196\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>This counts the number of erasers and pens. Note, this formula uses COUNTIF twice since there are multiple criteria being used, with one criterion per expression.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Limitations_of_the_COUNTIF_Formula\"><\/span>Limitations of the COUNTIF Formula<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>If your COUNTIF formula uses criteria matched to a string longer than 255 characters, it will return an error. To fix this, use the CONCATENATE function to match strings longer than 255 characters. You can avoid typing out the full function by simply using an ampersand (&amp;), as demonstrated below.<\/p>\n<pre>=COUNTIF(A2:A5,\"long string\"&amp;\"another long string\")<\/pre>\n<p><strong>RELATED:<\/strong> <strong><em>How to Use the FREQUENCY Function in Excel<\/em><\/strong><\/p>\n<p>One behavior of COUNTIF functions to be aware of is that it disregards upper and lower case strings. Criteria that include a lower case string (e.g. \u201cerasers\u201d) and an upper case string (e.g. \u201cERASERS\u201d) will match the same cells and return the same value.<\/p>\n<p>Another behavior of COUNTIF functions involves the use of wildcard characters. Using an asterisk in COUNTIF criteria will match any sequence of characters. For example, <code>=COUNTIF(A2:A5, \"*eraser*\")<\/code> will count all cells in a range that contain the word \u201ceraser.\u201d<\/p>\n<p>When you\u2019re counting values in a range, you may be interested in highlighting the top- or bottom-ranked values.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Highlight Top- or Bottom-Ranked Values in Microsoft Excel<\/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\/752592\/how-to-use-the-countif-formula-in-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;#How to Use the COUNTIF Formula in Microsoft Excel&#8221; In Microsoft Excel, COUNTIF is one of the most widely used formulas. It counts all cells in a range that matches a single condition or multiple conditions, and it\u2019s equally useful in counting cells with numbers and text in them. What Is the COUNTIF function? COUNTIF&#8230;<\/p>\n","protected":false},"author":1,"featured_media":353624,"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-353623","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\/353623","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=353623"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/353623\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/353624"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=353623"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=353623"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=353623"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}