Microsoft Office Development and Consultancy
 Home|VBA|C#|

Excel

|Word|PowerPoint|Outlook|Tools|Newsletter|Contact 
 Excel > Functions > Statistical > COUNTIF

 

COUNTIF(range, criteria)

 
 Returns the number of non blank cells that satisfy a particular condition.

 rangeThe range of cells from which you want to count the cells.
 criteriaThe logical test that will filter out the data.

 REMARKS
 
  • The "range" must be a cell range or a named range.
  •  
  • The "range" does not have to be sorted into any order.
  •  
  • The "criteria" can be a cell reference or a named range.
  •  
  • The "criteria" can be in the form of a number, expression, or text.
  •  
  • The "criteria" can be expressed as numerical (i.e. 32) or as a string (i.e. "32").
  •  
  • The "criteria" can use string matching, i.e. *M* is all words that contain the letter "M". This is not case sensitive.
  •  
  • If you are checking for numerical conditions make sure the cells contain numbers and not text.
  •  
  • Example 1 - This counts the number of cells that contain the text "apples".
  •  
  • Example 2 - This counts the number of cells that contain a negative number.
  •  
  • Example 3 - This counts the number of cells that contain a number less than 40.
  •  
  • Example 4 - This counts the number of cells that contain a number greater than 40.
  •  
  • Example 5 - This counts the number of cells that contain non zero values.
  •  
  • Example 6 - This counts the number of cells that contain a value between 1 and 100.
  •  
  • Example 7 - This counts the number of cells that contain text.
  •  
  • Example 8 - This counts the number of cells that contain the letter "B". This is not case sensitive.
  •  
  • Example 9 - This counts the number of cells that contain the letter "b". This is not case sensitive.
  •  
  • Example 10 - This counts the number of cells that contain only three letters.
  •  
  • Example 11 - This counts the number of cells that have the letter "e" as their second character.
  •  
  • Example 12 - This counts the number of cells that contain the text "Y".
  •  
  • Example 13 - This counts the number of cells that contain a date that is less than todays date.
  •  
  • Example 14 - This counts the number of cells that contain a date that is less than 30 days before todays date.
  •  
  • Example 15 - This counts the number of cells that contain either the text "Y" or the text "N".
  •  
  • For examples of how to include multiple conditions, please refer to Array Formulas > Multiple Conditions

  •  EXAMPLES
     
     ABC
    1=COUNTIF(B1:C8,"apples") = 02035
    2=COUNTIF(B1:C8,"<0") = 1-4085
    3=COUNTIF(B1:C8,"<40") = 360125
    4=COUNTIF(B1:C8,">40") = 718095
    5=COUNTIF(B1:C8,"<>0") = 1650055
    6=COUNTIF(B1:C8,">=1")-COUNTIF(B1:C8,">=100") = 6Betterdot
    7=COUNTIF(B1:C8,"*") = 6Solutionscom
    8=COUNTIF(B1:C8,"*B*") = 1YN
    9=COUNTIF(B1:C8,"*b*") = 112-Jul-200821-Jul-2020
    10=COUNTIF(B1:C8,"???") = 2  
    11=COUNTIF(B1:C8,"?e*") = 1  
    12=COUNTIF(B1:C8,"Y") = 1  
    13=COUNTIF(B9:C9,"<"&TODAY()) = 1  
    14=COUNTIF(B9:C9,"<"&TODAY()-30) = 1  
    15=COUNTIF(B1:C8,"Y")+COUNTIF(B1:C8,"N") = 2  
     

     Functions - C | Index - C | Office Online 2013 | 2010 | 2007 | 2003 

     © Better Solutions Limited 26-Mar-2014Top