COUNTIFS Function

Counts the number of cells within a range that meet all of the specified criteria.
Sample Usage
COUNTIFS(Quantity:Quantity, >25, [Item Name]:[Item Name], "T-Shirt")
Syntax
COUNTIFS(
  • range1
  • criterion1
  • [
    • range2
    • criterion2
    • ...
    ]
)
  • range1
    The group of cells to count.
  • criterion1
    The value that determines which cells in the range will be counted; for example: 15, "Hello World!", or >25.
    • range2
    • criterion2
    • ...
    —[optional]
    Additional groups of cells and their criteria.
Usage Notes
  • For criterion, acceptable operators include: = (equal to), <> (not equal to), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to).
  • Blank cells aren't counted when using <> (not equal to). For example, the formula =COUNTIFS(Item:Item, <> "Shirt",Warehouse:Warehouse, <> "Chicago") won't count any blank cells that exist in the Item and Warehouse columns.
Examples

This example references the following sheet information:

 Clothing ItemTransaction TotalUnits SoldIn Stock?
1T-Shirt1,170.0078true
2Pants1,491.0042false
3Jacket812.00217true

Given the table above, here are some examples of using COUNTIFS in a sheet:

FormulaDescriptionResult
=COUNTIFS([Units Sold]:[Units Sold], > 50, [In Stock?]:[In Stock?], 1)Count the number of rows where the value in the “Units Sold” column is greater than 50, and the “In Stock?" checkbox is checked. Rows 1 and 3 meet the criteria.2
=COUNTIFS([Clothing Item]:[Clothing Item], "Jacket", [In Stock?]:[In Stock?], 1)Count the number of rows where value in the “Clothing Item” column is “Jacket,” and the “In Stock?” checkbox is checked. Row 3 meets the criteria.1
=COUNTIFS([Transaction Total]:[Transaction Total], > 1000, [Units Sold]:[Units Sold], < 50)Count the number of rows where the value in the “Transaction Total” column is greater than 1,000, and the value in the  “Units Sold” column is less than 50. Row 2 meets the criteria.1
Was this article helpful?
YesNo