{"id":1024,"date":"2009-11-25T16:00:00","date_gmt":"2009-11-25T10:30:00","guid":{"rendered":"http:\/\/windowsvj.com\/wpblog\/2009\/11\/count-the-occurrences-of-a-text-in-excel\/"},"modified":"2009-11-25T16:00:00","modified_gmt":"2009-11-25T10:30:00","slug":"count-the-occurrences-of-a-text-in-excel","status":"publish","type":"post","link":"http:\/\/windowsvj.com\/wpblog\/2009\/11\/count-the-occurrences-of-a-text-in-excel\/","title":{"rendered":"Count the occurrences of a text in Excel"},"content":{"rendered":"<p>This article explains how you can use worksheet functions in Microsoft Excel to count the number of occurrences of a specific number or text string in a range of cells on a worksheet. <\/p>\n<p><a href=\"http:\/\/windowsvj.com\/wpblog\/wp-content\/uploads\/2009\/11\/excel.jpg\"><img loading=\"lazy\" decoding=\"async\" style=\"border-bottom: 0px; border-left: 0px; display: inline; margin-left: 0px; border-top: 0px; margin-right: 0px; border-right: 0px\" title=\"excel\" border=\"0\" alt=\"excel\" align=\"right\" src=\"http:\/\/windowsvj.com\/wpblog\/wp-content\/uploads\/2009\/11\/excel-thumb.jpg\" width=\"219\" height=\"244\" \/><\/a> <\/p>\n<p>In the cell that you want the result to appear in, enter the appropriate formula from the following examples. <\/p>\n<p><strong><u>How to Count the Occurrences of a Number<\/u><\/strong><\/p>\n<p> Use this formula   <\/p>\n<p>=SUM(IF(<var>range<\/var>=<var>number<\/var>,1,0)) <\/p>\n<p>where <var>range<\/var> is the range that you want to search, and <var>number<\/var> is the number that you want to count. <\/p>\n<p><b>NOTE<\/b>: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER. <\/p>\n<p><strong><u>How to Count the Occurrences of a Text String<\/u><\/strong><\/p>\n<p><strong>Method<\/strong> 1<\/p>\n<p> Use this formula   <\/p>\n<p>=SUM(IF(<var>range<\/var>=&quot;<var>text<\/var>&quot;,1,0)) <\/p>\n<p>where <var>range<\/var> is the range that you want to search, and <var>text<\/var> is the text that you want to find (the text must be enclosed in quotation marks).     <br \/><b>NOTE<\/b>: The above formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER. <\/p>\n<p><a href=\"http:\/\/windowsvj.com\/wpblog\/wp-content\/uploads\/2009\/11\/excel2.jpg\"><img loading=\"lazy\" decoding=\"async\" style=\"border-bottom: 0px; border-left: 0px; display: block; float: none; margin-left: auto; border-top: 0px; margin-right: auto; border-right: 0px\" title=\"excel2\" border=\"0\" alt=\"excel2\" src=\"http:\/\/windowsvj.com\/wpblog\/wp-content\/uploads\/2009\/11\/excel2-thumb.jpg\" width=\"244\" height=\"58\" \/><\/a> <\/p>\n<p>In the screenshots we have calculated number of occurrences of text \u201cpending\u201d in column \u2018G\u2019 <\/p>\n<p><strong>Method 2<\/strong><\/p>\n<p> Use the COUNTIF() function to count the occurrences of a text string. For example, use the formula   <\/p>\n<p>=COUNTIF(<var>range<\/var>,&quot;<var>text<\/var>&quot;) <\/p>\n<p>where <var>range<\/var> is the range of cells that you are evaluating, and <var>text<\/var> is the text string that you want to count instances of (note that <var>text<\/var> must be enclosed in quotation marks).     <br \/><b>NOTE<\/b>: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.     <br \/>Wildcard characters can be used within the COUNTIF function.     <br \/>The asterisk character (*) represents more than one character. For example, to count all the cells in the range a1:a10 that contain an &quot;x,&quot; you can use the following formula: <\/p>\n<p>=COUNTIF(a1:a10,&quot;*x*&quot;) <\/p>\n<p>The question mark character (?) can also be used to represent one wildcard character &#8212; for example, to count all cells in the range whose second character is the letter, such as &quot;ax&quot; or &quot;bx.&quot; <\/p>\n<p>=COUNTIF(a1:a10,&quot;?x*&quot;) <\/p>\n","protected":false},"excerpt":{"rendered":"<p>This article explains how you can use worksheet functions in Microsoft Excel to count the number of occurrences of a specific number or text string in a range of cells on a worksheet. In the cell that you want the result to appear in, enter the appropriate formula from the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[92],"class_list":["post-1024","post","type-post","status-publish","format-standard","hentry","category-office-2010","tag-excel"],"_links":{"self":[{"href":"http:\/\/windowsvj.com\/wpblog\/wp-json\/wp\/v2\/posts\/1024","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/windowsvj.com\/wpblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/windowsvj.com\/wpblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/windowsvj.com\/wpblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/windowsvj.com\/wpblog\/wp-json\/wp\/v2\/comments?post=1024"}],"version-history":[{"count":0,"href":"http:\/\/windowsvj.com\/wpblog\/wp-json\/wp\/v2\/posts\/1024\/revisions"}],"wp:attachment":[{"href":"http:\/\/windowsvj.com\/wpblog\/wp-json\/wp\/v2\/media?parent=1024"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/windowsvj.com\/wpblog\/wp-json\/wp\/v2\/categories?post=1024"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/windowsvj.com\/wpblog\/wp-json\/wp\/v2\/tags?post=1024"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}