{"id":334,"date":"2013-01-03T21:14:22","date_gmt":"2013-01-03T15:44:22","guid":{"rendered":"http:\/\/exceltrick.com\/?p=334"},"modified":"2020-11-22T23:48:57","modified_gmt":"2020-11-22T18:18:57","slug":"excel-countif-function","status":"publish","type":"post","link":"https:\/\/exceltrick.com\/functions\/excel-countif-function\/","title":{"rendered":"Excel COUNTIF Function \u2013 How to Use"},"content":{"rendered":"<p>As the name suggests <i>Excel COUNTIF Function<\/i> is a combination of Count and <a title=\"Excel IF Statement \u2013 How to Use\" href=\"http:\/\/exceltrick.com\/functions\/excel-if-statement\/\"  rel=\"noopener noreferrer\">IF formula<\/a>. In plain English, COUNTIF Function can be described as a formula that can be used for counting the number of cells that\u00a0fulfill\u00a0a particular condition, within a predefined range.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_53 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\" role=\"button\"><label for=\"item-69f1b0f4d53c5\" ><span class=\"\"><span style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" 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: #999;color:#999\" 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 aria-label=\"Toggle\" aria-label=\"item-69f1b0f4d53c5\"  type=\"checkbox\" id=\"item-69f1b0f4d53c5\"><\/a><\/span><\/div>\n<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:\/\/exceltrick.com\/functions\/excel-countif-function\/#How_Excel_Defines_COUNTIF_Function\" title=\"How Excel Defines COUNTIF Function\">How Excel Defines COUNTIF Function<\/a><ul class='ez-toc-list-level-3'><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/exceltrick.com\/functions\/excel-countif-function\/#Syntax_of_Excel_COUNTIF_Formula\" title=\"Syntax of Excel COUNTIF Formula\">Syntax of Excel COUNTIF Formula<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/exceltrick.com\/functions\/excel-countif-function\/#How_to_Use_COUNTIF_in_Excel\" title=\"How to Use COUNTIF in Excel\">How to Use COUNTIF in Excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/exceltrick.com\/functions\/excel-countif-function\/#Few_Important_Facts_About_the_COUNTIF_Formula\" title=\"Few Important Facts About the COUNTIF Formula\">Few Important Facts About the COUNTIF Formula<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/exceltrick.com\/functions\/excel-countif-function\/#Few_Basic_Examples_of_COUNTIF_Function\" title=\"Few Basic Examples of COUNTIF Function\">Few Basic Examples of COUNTIF Function<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/exceltrick.com\/functions\/excel-countif-function\/#Few_Advanced_Examples_of_COUNTIF_Function\" title=\"Few Advanced Examples of COUNTIF Function\">Few Advanced Examples of COUNTIF Function<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"How_Excel_Defines_COUNTIF_Function\"><\/span>How Excel Defines COUNTIF Function<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Microsoft Excel defines COUNTIF as a formula that, \u201cCounts the number of cells within a range that meet the given condition\u201d.<\/p>\n<p>This definition clearly explains that: COUNTIF Function is a better and sophisticated type of COUNT formula that gives you control over, which cells you wish to count.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Syntax_of_Excel_COUNTIF_Formula\"><\/span>Syntax of Excel COUNTIF Formula<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Excel COUNTIF formula can be written as follows:<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">COUNTIF<\/span>(range , criteria)<\/code><\/div>\n<p>Here \u2018<code>range<\/code>\u2019 specifies the range of cells over which you want to apply the &#39;<code>criteria<\/code>&#39;.<\/p>\n<p>\u2018<code>criteria<\/code>\u2019 specifies the condition that a particular cell content should meet to be counted.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"How_to_Use_COUNTIF_in_Excel\"><\/span>How to Use COUNTIF in Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Now, let\u2019s see how to use the COUNTIF function in Excel.<\/p>\n<p>Let\u2019s consider, we have an Employee table as shown in the below image.<\/p>\n<a href=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/How-to-Use-CountiF-01.png\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-986\" src=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/How-to-Use-CountiF-01.png\" alt=\"Employee Table\" width=\"360\" height=\"249\" title=\"\"><\/a>\n<p><span style=\"text-decoration: underline;\">Objective:<\/span> From the above table, our objective is to find the number of employees who have joined before 1990.<\/p>\n<p>So, we will try to use the COUNTIF Formula to find the result.<\/p>\n<a href=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/How-to-Use-CountiF-02.png\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-987\" src=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/How-to-Use-CountiF-02.png\" alt=\"Using Countif 01\" width=\"692\" height=\"289\" title=\"\"><\/a>\n<p><span style=\"text-decoration: underline;\">range:<\/span> In this case, \u2018<code>range<\/code>\u2019 will be \u201cB2:B11\u201d, as on these cells we have to apply the \u2018<code>criteria<\/code>\u2019.<\/p>\n<a href=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/How-to-Use-CountiF-03.png\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-988\" src=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/How-to-Use-CountiF-03.png\" alt=\"Using CountIf 02\" width=\"690\" height=\"289\" title=\"\"><\/a>\n<p><span style=\"text-decoration: underline;\">criteria:<\/span> In this case, \u2018\u00a0<code>criteria<\/code>\u2019 is \u201c&lt;01\/01\/1990\u201d. This specifies that we want to count only those employees that are joined before 1<sup>st<\/sup> January 1990.<\/p>\n<a href=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/How-to-Use-CountiF-04.png\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-989\" src=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/How-to-Use-CountiF-04.png\" alt=\"Result of CountIf\" width=\"690\" height=\"289\" title=\"\"><\/a>\n<p>This results in 6, which means there are 6 employees that have joined before 1990.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Few_Important_Facts_About_the_COUNTIF_Formula\"><\/span>Few Important Facts About the COUNTIF Formula<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>1. COUNTIF formula only accepts a solid range, you cannot give multiple broken ranges to it. For example, COUNTIF <b>cannot<\/b> be written as<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">COUNTIF<\/span>(A1:A4 , A6:A8, \"<span class=\"string\">&gt;0<\/span>\") <span class=\"comment\">\/\/This is wrong<\/span><br \/>\n<span class=\"operator\">=<\/span><span class=\"function\">COUNTIF<\/span>(A1:A8, \"<span class=\"string\">&gt;0<\/span>\") <span class=\"comment\">\/\/This is correct<\/span><br \/>\n<\/code><\/div>\n<p>2. COUNTIF can accept wildcard characters (like \u201c*\u201d and \u201c?\u201d) in the \u2018<code>criteria<\/code>\u2019 argument. This means that you <b>can<\/b> write a COUNTIF as<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">COUNTIF<\/span>(D1:D15, \"<span class=\"string\">*o*<\/span>\")<\/code><\/div>\n<p>This will count all the cells containing the \u201co\u201d character, within the D1:D5 range.<\/p>\n<p>3. As you know, the output of COUNTIF is an integer so you <b>can<\/b> also add two COUNTIF functions. For example: if you want to find the cells with value as \u201c1\u201d and cells with value as \u201c2\u201d, so you can use COUNTIF as<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">COUNTIF<\/span>(A1:A10,\"<span class=\"string\">1<\/span>\")<span class=\"operator\">+<\/span><span class=\"function\">COUNTIF<\/span>(A1:A10,\"<span class=\"string\">2<\/span>\")<\/code><\/div>\n<p>4. COUNTIF throws a <code>#NAME?<\/code> error, if you supply an incorrect range to it.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Few_Basic_Examples_of_COUNTIF_Function\"><\/span>Few Basic Examples of COUNTIF Function<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<a href=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/excel-countif-function-example.png\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-335\" src=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/excel-countif-function-example.png\" alt=\"Excel COUNTIF Function\" width=\"660\" height=\"402\" title=\"\"><\/a>\n<p>In the above image, I have used an Employee table to depict how the COUNTIF function can be used.<\/p>\n<p><b>Example 1:<\/b> In the first example, I have used the Excel COUNTIF formula for finding the number of employees whose first name starts with \u201cG\u201d.<\/p>\n<p>For this, I have used formula as<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">COUNTIF<\/span>(A3:A12,\"<span class=\"string\">G*<\/span>\")<\/code><\/div>\n<p>Here, the COUNTIF Function scans the whole range from A3:A12 and tries to find a pattern \u201cG*\u201d (\u2018*\u2019 is a wildcard operator which denotes any number of characters). The resultant is 2, as there are only two persons in the specified range whose first name starts with G.<\/p>\n<p><b>Example 2:<\/b> In the second example, I have used a COUNTIF function to find the cells which contain an Employee ID value greater than \u201c26000\u201d.<\/p>\n<p>To accomplish this I have used a formula<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">COUNTIF<\/span>(C3:C12,\"<span class=\"string\">&gt;26000<\/span>\")<\/code><\/div>\n<p>This formula searches the specified range for a value that specifies the criteria (i.e. &gt;26000). So, the result is 5 as only 5 employees have an Employee ID greater than 26000.<\/p>\n<p><b>Example 3:<\/b> In the third example, I have fetched the number of employees whose salary is less than 4000.<\/p>\n<p>To get this, I have again used a COUNTIF formula as<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">COUNTIF<\/span>(D3:D12,\"<span class=\"string\">&lt;4000<\/span>\")<\/code><\/div>\n<p>So, here the COUNTIF counts only those cells where salary range i.e. D3:D12 has a value less than 4000 and the resultant is 3.<\/p>\n<p><b>Example 4:<\/b> In the fourth example, I have used the following\u00a0 formula<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">COUNTIF<\/span>(B3:B12,B5)<\/code><\/div>\n<p>This formula finds the number of cells equal to the value of the cell B5 (i.e. \u201cMassiot\u201d), in the range B3:B15.<\/p>\n<p>Here, first, the COUNTIF function finds the value at the B5 cell, and then it compares all the cells within the specified range with this value.<\/p>\n<p>The resultant is 2 as only two records match the value at the B5 cell.<\/p>\n<a href=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/excel-countif-function-example-2.png\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-336\" src=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/excel-countif-function-example-2.png\" alt=\"Excel COUNTIF Function Addition\" width=\"637\" height=\"248\" title=\"\"><\/a>\n<p><b>Example 5:<\/b> In the above example, I had to find the total count of cells that contain \u201cApple\u201d or \u201cPeach\u201d.<\/p>\n<p>This can be easily done by adding the resultants of two COUNTIF statements like:<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">COUNTIF<\/span>(A2:A7,\"<span class=\"string\">Apple<\/span>\")<span class=\"operator\">+<\/span><span class=\"function\">COUNTIF<\/span>(A2:A7,\"<span class=\"string\">Peach<\/span>\")<\/code><\/div>\n<p>The first COUNTIF statement gives the number of cells with a value equal to \u201cApple\u201d and the second statement gives the count of cells with \u201cPeach\u201d. And hence the output comes out as 2+1=3.<\/p>\n<p><b>Example 6:<\/b> In this example (i.e. <code>=COUNTIF(A,\"Pear\")<\/code>), I have tried to show you what happens if you enter an incorrect range in the COUNTIF function.<\/p>\n<p>In such cases, it throws a <code>#NAME?<\/code> error.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Few_Advanced_Examples_of_COUNTIF_Function\"><\/span>Few Advanced Examples of COUNTIF Function<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Now, let\u2019s see some practical examples of COUNTIF Function.<\/p>\n<p><b>Example 7: <\/b>Finding duplicate values using the COUNTIF function.<\/p>\n<p>Let\u2019s say we have a table as below, and we have to find the duplicate records in it.<\/p>\n<a href=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/Practical-Examples-of-CountiF-05.png\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-990\" src=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/Practical-Examples-of-CountiF-05.png\" alt=\"Find Duplicates Using CountIF\" width=\"364\" height=\"325\" title=\"\"><\/a>\n<p>For finding the duplicate records, we have used the formula:<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">COUNTIF<\/span>($A$2:$A$16,A2)<span class=\"operator\">&gt;<\/span>1<\/code><\/div>\n<p>When this formula encounters a duplicate record it returns TRUE, while FALSE means that the record is Unique.<\/p>\n<a href=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/Practical-Examples-of-CountiF-06.png\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-991\" src=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/Practical-Examples-of-CountiF-06.png\" alt=\"Find Duplicates using CountIF 02\" width=\"364\" height=\"325\" title=\"\"><\/a>\n<p>If you are wondering what these dollar ($) signs are doing in this formula, then you should <a title=\"Excel Relative and Absolute References \u2013 Explained\" href=\"http:\/\/exceltrick.com\/what-is\/excel-relative-and-absolute-references\/\"  rel=\"noopener noreferrer\">read this post<\/a>.<\/p>\n<p><i>Recommended Reading: <\/i><a title=\"Find and Delete Duplicates in Excel \u2013 The Ultimate Guide\" href=\"http:\/\/exceltrick.com\/how-to\/find-and-delete-duplicates-in-excel\/\"  rel=\"noopener noreferrer\">Find and Delete Duplicate cells in Excel<\/a><i><\/i><\/p>\n<p><b>Example 8: <\/b>Use the COUNTIF formula for generating the sorting order of a list.<\/p>\n<p>Let\u2019s consider we have a list as below.<\/p>\n<a href=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/Practical-Examples-of-CountiF-07.png\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-992\" src=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/Practical-Examples-of-CountiF-07.png\" alt=\"Use Countif to find the Sorting order of a list\" width=\"448\" height=\"322\" title=\"\"><\/a>\n<p>Now, If we just want to know the alphabetic sorting order (in ascending order) of the employee names, then we can use the formula:<\/p>\n<div class=\"excelFormula\"><code><span class=\"operator\">=<\/span><span class=\"function\">COUNTIF<\/span>($A$2:$A$15,\"<span class=\"string\">&lt;=<\/span>\"<span class=\"operator\">&amp;<\/span>A2)<\/code><\/div>\n<p>See the below image, to see this formula in action.<\/p>\n<a href=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/Practical-Examples-of-CountiF-08.png\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-985\" src=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/Practical-Examples-of-CountiF-08.png\" alt=\"Use Countif to find the Sorting order of a list 02\" width=\"447\" height=\"322\" title=\"\"><\/a>\n<p>As you can see, that this formula generates a number in-front of every employee. This number is the sorting order (in ascending sort) of the Employee Names.<\/p>\n<p><i>Recommended Reading: <\/i><a title=\"How to Alphabetize in Excel \u2013 The Complete Guide\" href=\"http:\/\/exceltrick.com\/how-to\/alphabetize-in-excel\/\"  rel=\"noopener noreferrer\">How to alphabetize a list in Excel<\/a><\/p>\n<p>So, this was all from my side. Do share your ideas and experiences related to <strong>Excel COUNTIF Function<\/strong> in the below comments section.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>As the name suggests Excel COUNTIF Function is a combination of Count and IF formula. In plain English, COUNTIF Function can be described as a formula that can be used for counting the number of cells that\u00a0fulfill\u00a0a particular condition, within a predefined range. How Excel Defines COUNTIF Function Microsoft Excel defines COUNTIF as a formula [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[9],"_links":{"self":[{"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/posts\/334"}],"collection":[{"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/comments?post=334"}],"version-history":[{"count":0,"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/posts\/334\/revisions"}],"wp:attachment":[{"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/media?parent=334"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/categories?post=334"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/tags?post=334"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}