COUNTIFS Function

Counts the number of times all given conditions in their respective ranges are met.
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?" The 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?” The 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

Still need help?

Use the Formula Handbook template to find more support resources, and view 100+ formulas, including a glossary of every function that you can practice working with in real time, and examples of commonly used and advanced formulas.

Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.

Ask the Community

Was this article helpful?
YesNo