{"id":1782,"date":"2011-10-05T10:06:47","date_gmt":"2011-10-05T10:06:47","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/exceltraining\/?p=302"},"modified":"2024-05-12T01:22:55","modified_gmt":"2024-05-12T01:22:55","slug":"using-the-countif-function-in-excel-2010","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/using-the-countif-function-in-excel-2010\/","title":{"rendered":"Using the Countif Function in Excel 2010"},"content":{"rendered":"

Countif<\/strong> Counts the number of cells within a range that meet the given criteria.<\/p>\n

Syntax<\/h2>\n

COUNTIF<\/strong>(range<\/strong>,criteria<\/strong>)<\/p>\n

Range<\/strong>\u00a0\u00a0\u00a0\u00a0is the range of cells from which you want to count cells.<\/p>\n

Criteria<\/strong>\u00a0\u00a0\u00a0\u00a0is the criteria in the form of a number, expression, cell reference, or text that defines which cells will be counted. For example, criteria can be expressed as 32, “32”, “>32”, “apples”, or B4.<\/p>\n

Remarks<\/h2>\n
    \n
  • You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.<\/li>\n
  • Microsoft Excel provides additional functions that can be used to analyze your data based on a condition.<\/li>\n
      \n
    • To calculate a sum based on a string of text or a number within a range, use the SUMIF worksheet function.<\/li>\n
    • To have a formula return one of two values based on a condition, such as a sales bonus based on a specified sales amount, use the IF worksheet function.<\/li>\n
    • To count cells that are empty or not empty, use the COUNTA and COUNTBLANK functions.<\/li>\n<\/ul>\n<\/ul>\n

      Example 1: Common COUNTIF formulas<\/h2>\n

      The example may be easier to understand if you copy it to a blank worksheet.<\/p>\n

      \n
        \n
      1. Create a blank workbook or worksheet.<\/li>\n
      2. Select the example in the Help topic.<\/li>\n<\/ol>\n

        \u00a0Note\u00a0<\/strong>\u00a0\u00a0Do not select the row or column headers.<\/p>\n

        Selecting an example from Help<\/div>\n
          \n
        1. Press CTRL+C.<\/li>\n
        2. In the worksheet, select cell A1, and press CTRL+V.<\/li>\n
        3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools<\/strong> menu, point to Formula Auditing<\/strong>, and then click Formula Auditing Mode<\/strong>.<\/li>\n<\/ol>\n<\/div>\n\n\n\n
          \n
          \n\n\n\n\n\n\n\n\n
          \u00a0<\/td>\n<\/tr>\n
          1<\/strong><\/th>\n<\/tr>\n
          2<\/strong><\/th>\n<\/tr>\n
          3<\/strong><\/th>\n<\/tr>\n
          4<\/strong><\/th>\n<\/tr>\n
          5<\/strong><\/th>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/td>\n\n
          \n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
          A<\/strong><\/th>\nB<\/strong><\/th>\n<\/tr>\n
          Data<\/strong><\/td>\nData<\/strong><\/td>\n<\/tr>\n
          apples<\/td>\n32<\/td>\n<\/tr>\n
          oranges<\/td>\n54<\/td>\n<\/tr>\n
          peaches<\/td>\n75<\/td>\n<\/tr>\n
          apples<\/td>\n86<\/td>\n<\/tr>\n
          Formula<\/strong><\/td>\nDescription (result)<\/strong><\/td>\n<\/tr>\n
          =COUNTIF(A2:A5,”apples”)<\/td>\nNumber of cells with apples in the first column above (2)<\/td>\n<\/tr>\n
          =COUNTIF(A2:A5,A4)<\/td>\nNumber of cells with peaches in the first column above (1)<\/td>\n<\/tr>\n
          =COUNTIF(A2:A5,A3)+COUNTIF(A2:A5,A2)<\/td>\nNumber of cells with oranges or apples in the first column above (3)<\/td>\n<\/tr>\n
          =COUNTIF(B2:B5,”>55″)<\/td>\nNumber of cells with a value greater than 55 in the second column above (2)<\/td>\n<\/tr>\n
          =COUNTIF(B2:B5,”<>”&B4)<\/td>\nNumber of cells with a value not equal to 75 in the second column above (2)<\/td>\n<\/tr>\n
          =COUNTIF(B2:B5,”>=32″)-COUNTIF(B2:B5,”>85″)<\/td>\nNumber of cells with a value greater than or equal to 32 and less than or equal to 85 in the second column above (3)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

          Example 2: COUNTIF formulas using wildcard characters and handling blank values<\/h2>\n

          The example may be easier to understand if you copy it to a blank worksheet.<\/p>\n

          Create a blank workbook or worksheet.<\/p>\n

          \n
            \n
          1. Select the example in the Help topic.<\/li>\n<\/ol>\n

            \u00a0Note\u00a0<\/strong>\u00a0\u00a0Do not select the row or column headers.<\/p>\n

            Selecting an example from Help<\/div>\n
              \n
            1. Press CTRL+C.<\/li>\n
            2. In the worksheet, select cell A1, and press CTRL+V.<\/li>\n
            3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools<\/strong> menu, point to Formula Auditing<\/strong>, and then click Formula Auditing Mode<\/strong>.<\/li>\n<\/ol>\n<\/div>\n\n\n\n
              \n
              \n\n\n\n\n\n\n\n\n\n\n
              \u00a0<\/td>\n<\/tr>\n
              1<\/strong><\/th>\n<\/tr>\n
              2<\/strong><\/th>\n<\/tr>\n
              3<\/strong><\/th>\n<\/tr>\n
              4<\/strong><\/th>\n<\/tr>\n
              5<\/strong><\/th>\n<\/tr>\n
              6<\/strong><\/th>\n<\/tr>\n
              7<\/strong><\/th>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/td>\n\n
              \n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
              A<\/strong><\/th>\nB<\/strong><\/th>\n<\/tr>\n
              Data<\/strong><\/td>\nData<\/strong><\/td>\n<\/tr>\n
              apples<\/td>\nYes<\/td>\n<\/tr>\n
              \u00a0<\/td>\n\u00a0<\/td>\n<\/tr>\n
              oranges<\/td>\nNO<\/td>\n<\/tr>\n
              peaches<\/td>\nNo<\/td>\n<\/tr>\n
              \u00a0<\/td>\n\u00a0<\/td>\n<\/tr>\n
              apples<\/td>\nYeS<\/td>\n<\/tr>\n
              Formula<\/strong><\/td>\nDescription (result)<\/strong><\/td>\n<\/tr>\n
              =COUNTIF(A2:A7,”*es”)<\/td>\nNumber of cells ending with the letters “es” in the first column above (4)<\/td>\n<\/tr>\n
              =COUNTIF(A2:A7,”?????es”)<\/td>\nNumber of cells ending with the letters “les” and having exactly 7 letters in the first column above (2)<\/td>\n<\/tr>\n
              =COUNTIF(A2:A7,”*”)<\/td>\nNumber of cells containing text in the first column above (4)<\/td>\n<\/tr>\n
              =COUNTIF(A2:A7,”<>”&”*”)<\/td>\nNumber of cells not containing text in the first column above (2)<\/td>\n<\/tr>\n
              =COUNTIF(B2:B7,”No”) \/ ROWS(B2:B7)<\/td>\nThe average number of No votes including blank cells in the second column above formatted as a percentage with no decimal places (33%)<\/td>\n<\/tr>\n
              =COUNTIF(B2:B7,”Yes”) \/ (ROWS(B2:B7) -COUNTIF(B2:B7, “<>”&”*”))<\/td>\nThe average number of Yes votes excluding blank cells in the second column above formatted as a percentage with no decimal places (50%)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

              \u00a0Note\u00a0<\/strong>\u00a0\u00a0 To view the number as a percentage, select the cell and click Cells<\/strong> on the Format<\/strong> menu. Click the Number<\/strong> tab, and then click Percentage<\/strong> in the Category<\/strong> box.<\/p>\n","protected":false},"excerpt":{"rendered":"

              Countif Counts the number of cells within a range that meet the given criteria. Syntax COUNTIF(range,criteria) Range\u00a0\u00a0\u00a0\u00a0is the range of cells from which you want to count cells. Criteria\u00a0\u00a0\u00a0\u00a0is the criteria in the form of a number, expression, cell reference, or text that defines which cells will be counted. For example, criteria can be expressed […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4],"tags":[433,568],"class_list":["post-1782","post","type-post","status-publish","format-standard","hentry","category-excel-training","tag-pr","tag-using-the-countif-function-in-excel-2010"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/1782","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=1782"}],"version-history":[{"count":2,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/1782\/revisions"}],"predecessor-version":[{"id":7544,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/1782\/revisions\/7544"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=1782"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=1782"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=1782"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}