Excel COUNTIFS Function

The Excel COUNTIFS function returns the number of cells in a range that meet one or more specified criteria

Example: Excel COUNTIFS Function

Excel COUNTIFS Function

METHOD 1. Excel COUNTIFS Function using hardcoded values

EXCEL

=COUNTIFS(C10:C16,">500",C10:C16,"<1000")
Result in cell E10 (2) - returns the number of cells that are greater than 500 but less than 1000, from the same range (C10:C16).

=COUNTIFS(C10:C16,">500",B10:B16,"Apples")
Result in cell E11 (1) - returns the number of cells that are greater than 500 and apple as the product, associated with ranges (C10:C16) and (B10:B16), respectively.

METHOD 2. Excel COUNTIFS Function using links

EXCEL

=COUNTIFS(C10:C16,">"&C5,C10:C16,"<"&C6)
Result in cell E10 (2) - returns the number of cells that are greater than the value in cell (C5) but less than the value in cell (C6), from the same range (C10:C16).

=COUNTIFS(C10:C16,">"&C5,B10:B16,C7)
Result in cell E11 (1) - returns the number of cells that are greater than the value in cell (C5) and equal to the value in cell (C7), associated with ranges (C10:C16) and (B10:B16), respectively.

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

EXCEL

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

=COUNTIFS(C10:C16,">500",C10:C16,"<1000")
Note: in this example we are populating COUNTIFS function input boxes that are associated with two criteria. 		Built-in Excel COUNTIFS Function using hardocded values

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

EXCEL

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

=COUNTIFS(C10:C16,">"&C5,C10:C16,"<"&C6)
Note: in this example we are populating COUNTIFS function input boxes that are associated with two criteria. 		Built-in Excel COUNTIFS Function using links

METHOD 1. Excel COUNTIFS function using VBA with hardcoded values

VBA

Sub Excel_COUNTIFS_Function()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("COUNTIFS")

'apply the Excel COUNTIFS function
ws.Range("E10") = Application.WorksheetFunction.CountIfs(ws.Range("C10:C16"), ">500", ws.Range("C10:C16"), "<1000")
ws.Range("E11") = Application.WorksheetFunction.CountIfs(ws.Range("C10:C16"), ">500", ws.Range("B10:B16"), "Apples")

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 COUNTIFS.
Data Range: Ensure that the data that you want to count from is captured in range ("C10:C16") and ("B10:B16") in the COUNTIFS worksheet.
Specific Value: Input the specific value that you want to test for in the VBA code by replacing 500, 1000 and Apples.

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

METHOD 2. Excel COUNTIFS function using VBA with links

VBA

Sub Excel_COUNTIFS_Function()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("COUNTIFS")

'apply the Excel COUNTIFS function
ws.Range("E10") = Application.WorksheetFunction.CountIfs(ws.Range("C10:C16"), ">" & ws.Range("C5"), ws.Range("C10:C16"), "<" & ws.Range("C6"))
ws.Range("E11") = Application.WorksheetFunction.CountIfs(ws.Range("C10:C16"), ">" & ws.Range("C5"), ws.Range("B10:B16"), ws.Range("C7"))

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 COUNTIFS.
Data Range: Ensure that the data that you want to count from is captured in range ("C10:C16") and ("B10:B16") in the COUNTIFS worksheet.
Specific Value: Input the specific value that you want to test for in cell ("C5"), ("C6") and ("C7") in the COUNTIFS worksheet.

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

Usage of the Excel COUNTIFS function and formula syntax

EXPLANATION

DESCRIPTION
The Excel COUNTIFS function returns the number of cells in a range that meet one or more specified criteria.
SYNTAX
=COUNTIFS(criteria_range1, criteria1, [criteria_rangle2], [criteria2], ...)
ARGUMENTS
criteria_range1: (Required) The range of cells that you want to apply the criteria1 against.
criteria1: (Required) The criteria to be tested against criteria_range1.
criteria_range2: (Optional) The range of cells that you want to apply the criteria2 against.
criteria2: (Optional) The criteria to be tested against criteria_range2.

ADDITIONAL NOTES
Note 1: Excel allows up to 127 criteria_range and criteria arguments.