COUNTIF Function | Excel

Excel provides conditional functions which allow the user to count all the cells in a range that meets single or multiple criteria. The following paragraphes will explain two types of these functions called "Countif" and "Countifs". They could check single and multiple criteria, respectively. This tutorial assumes you have a working knowledge of Average & If functions.

COUNTIF

The Countif function or statement is used to count all the cells in a range that meets single criteria. Typically, this function consists of two arguments (parts) enclosed in one set of parentheses and each is separated by a comma, as shown below:

=COUNTIF(range,criteria)

Where,

• ● RANGE is one or more cells to count, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
• ● CRITERIA is 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", "balls", or B4.

The formula can be inserted either by typing it or by selecting it from the Function Library on the formula’s ribbon, under heading of more functions in statistical. The Function Library allow to view the above syntax (details) of COUNTIF function, using Mouse as follow:

• i. Click on the FORMULAS Ribbon.
• ii. Click the drop down arrow below the MORE FUNCTIONS button, go to STATISTICAL and select COUNTIF from the displayed functions.
• 
  
  
  
• iii. The next dialog box will appear and the two arguments can be seen within the box.
• 
  
  
  
• iv. Click in RANGE text box.
• v. Select the range of cells you wish to check and count.
• vi. Click in the CRITERIA box, either, type criteria directly in the box or select a cell that contains the value to check against your RANGE and Click OK.

In next example, returns the count of B2:B5 where the corresponding value in column A contains a certain fruit name:

Countifs

Countifs function or statement is used to count all the cells in a range that meets multiple criteria. Typically, this function consists of more than or equal to three arguments (parts) enclosed in one set of parentheses and each is separated by a comma, as shown below:

=COUNTIFS(range1, criteria1,range2, criteria2…)

Where,

• ● RANGE1, RANGE2, … are 1 to 127 ranges in which to evaluate the associated criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
• ● CRITERIA1, CRITERIA2, …are 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", "balls", or B4.

The formula can be inserted either by typing it or by selecting it from the Function Library on the formula’s ribbon, under heading of more functions in statistical. The Function Library allow to view the above syntax (details) of COUNTIFS function, using Mouse as follow:

• i. Click on the FORMULAS Ribbon.
• ii. Click the drop down arrow below the MORE FUNCTIONS button, go to STATISTICAL and select COUNTIFS from the displayed functions.
• iii. The next dialog box will appear and more than three arguments can be seen within the box.
• 
  
  
  
• vi. Click in the CRITERIA_RANGE1 box select the range of cells that you wish to count.
• v. Click in the CRITERIA1 text box and type in the criteria to measure against your CRITERIA_RANGE1.
• vi. Repeat step vi and vi to enter multiple criteria, criteria_range2, range3 etc, more text boxes will appear after filling-in each criteria_range and criteria. Click OK when all ranges and criterias have been entered.

Some important points about COUNTIFS:

• ☺ Each cell in a range is counted only if all of the corresponding criteria specified are true for that cell.
• ☺ If criteria is an empty cell, COUNTIFS treats it as a 0 value.
• ☺ Wildcard characters, question mark (?) and asterisk (*), could be used in criteria. Find more explanation on "The Use of Wildcard Characters" in Sumif Tutorial.

In next example, the number of times for saler(s) who were able to exceed quota are calculated matching multipile criteria for several products.