Excel COUNTIF Function

The Excel COUNTIF function returns the number of cells in a range that meet a specified criteria

Example: Excel COUNTIF Function

Excel COUNTIF Function

METHOD 1. Excel COUNTIF Function using hardcoded values

EXCEL

=COUNTIF(C8:C14,500)
Result in cell E8 (1) - returns the number of cells that are equal to 500 in range (C8:C14).

=COUNTIF(C8:C14,">500")
Result in cell E9 (3) - returns the number of cells that are greater than 500 in range (C8:C14).

=COUNTIF(C8:C14,"<500")
Result in cell E10 (3) - returns the number of cells that are less than 500 in range (C8:C14).

=COUNTIF(C8:C14,"<=500")
Result in cell E11 (4) - returns the number of cells that are less than or equal to 500 in range (C8:C14).

=COUNTIF(C8:C14,">=500")
Result in cell E12 (4) - returns the number of cells that are greater than or equal to 500 in range (C8:C14).

METHOD 2. Excel COUNTIF Function using links

EXCEL

=COUNTIF(C8:C14,C5)
Result in cell E8 (1) - returns the number of cells that are equal to the same value as in cell (C5) in range (C8:C14).

=COUNTIF(C8:C14,">"&C5)
Result in cell E9 (3) - returns the number of cells that are greater than the value in cell (C5) in range (C8:C14).

=COUNTIF(C8:C14,"<"&C5)
Result in cell E10 (3) - returns the number of cells that are less than the value in cell (C5) in range (C8:C14).

=COUNTIF(C8:C14,"<="&C5)
Result in cell E11 (4) - returns the number of cells that are less than or equal to the value in cell (C5) in range (C8:C14).

=COUNTIF(C8:C14,">="&C5)
Result in cell E12 (4) - returns the number of cells that are greater than or equal to the value in cell (C5) in range (C8:C14).

METHOD 3. Excel COUNTIF function using the Excel built-in function library with hardcoded value

EXCEL

Formulas tab > Function Library group > More Functions > Statistical > COUNTIF > populate the input boxes

=COUNTIF(C8:C14,">500")
Note: in this example we are populating both of the input boxes, as they are both required COUNTIF arguments, with a criteria of greater than 500. 		Built-in Excel COUNTIF Function using hardocded values

METHOD 4. Excel COUNTIF function using the Excel built-in function library with links

EXCEL

Formulas tab > Function Library group > More Functions > Statistical > COUNTIF > populate the input boxes

=COUNTIF(C8:C14,">"&C5)
Note: in this example we are populating both of the input boxes, as they are both required COUNTIF arguments, with a criteria of greater than the value in cell (C5). 		Built-in Excel COUNTIF Function using links

METHOD 1. Excel COUNTIF function using VBA with hardcoded values

VBA

Sub Excel_COUNTIF_Function()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("COUNTIF")

'apply the Excel COUNTIF function
ws.Range("E8") = Application.WorksheetFunction.CountIf(ws.Range("C8:C14"), 500)
ws.Range("E9") = Application.WorksheetFunction.CountIf(ws.Range("C8:C14"), ">500")
ws.Range("E10") = Application.WorksheetFunction.CountIf(ws.Range("C8:C14"), "<500")
ws.Range("E11") = Application.WorksheetFunction.CountIf(ws.Range("C8:C14"), "<=500")
ws.Range("E12") = Application.WorksheetFunction.CountIf(ws.Range("C8:C14"), ">=500")

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named COUNTIF.
Data Range: Ensure that the data that you want to count from is captured in range ("C8:C14") in the COUNTIF worksheet.

ADJUSTABLE PARAMETERS
Data Range: Select the range that you want to count from by changing the range ("C8:C14") to any range in the worksheet that doesn't conflict with the formula.
Output Range: Select the output range by changing the cell references ("E8"), ("E9"), ("E10"), ("E11") and ("E12") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.

METHOD 2. Excel COUNTIF function using VBA with links

VBA

Sub Excel_COUNTIF_Function()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("COUNTIF")

'apply the Excel COUNTIF function
ws.Range("E8") = Application.WorksheetFunction.CountIf(ws.Range("C8:C14"), ws.Range("C5"))
ws.Range("E9") = Application.WorksheetFunction.CountIf(ws.Range("C8:C14"), ">" & ws.Range("C5"))
ws.Range("E10") = Application.WorksheetFunction.CountIf(ws.Range("C8:C14"), "<" & ws.Range("C5"))
ws.Range("E11") = Application.WorksheetFunction.CountIf(ws.Range("C8:C14"), "<=" & ws.Range("C5"))
ws.Range("E12") = Application.WorksheetFunction.CountIf(ws.Range("C8:C14"), ">=" & ws.Range("C5"))

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named COUNTIF.
Data Range: Ensure that the data that you want to count from is captured in range ("C8:C14") in the COUNTIF worksheet.
Specific Value: Input the specific value that you want to test for in cell ("C5") in the COUNTIF worksheet.

ADJUSTABLE PARAMETERS
Specific Value: Select the specific value that you want to test for by changing the value in cell ("C5").
Data Range: Select the range that you want to count from by changing the range ("C8:C14") to any range in the worksheet that doesn't conflict with the formula.
Output Range: Select the output range by changing the Range references ("E8"), ("E9"), ("E10"), ("E11") and ("E12") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.

Usage of the Excel COUNTIF function and formula syntax

EXPLANATION

DESCRIPTION
The Excel COUNTIF function returns the number of cells in a range that meet a specified criteria.
SYNTAX
=COUNTIF(range, criteria)

ARGUMENTS
range: (Required) The range of cells you want to count from.
criteria: (Required) The criteria that is used to determine which of the cells from the specified range should be counted.