Counts the number of times all given conditions in their respective ranges are met.
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.
Sample usage
COUNTIFS(Quantity:Quantity, >25, [Item Name]:[Item Name], "T-Shirt")
Usage notes
- The function only counts rows where all criteria are true.
- 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 Item | Transaction Total | Units Sold | In Stock? (Checkbox column type) | |
|---|---|---|---|---|
| 1 | T-Shirt | 1,170.00 | 78 | true |
| 2 | Pants | 1,491.00 | 42 | false |
| 3 | Jacket | 812.00 | 217 | true |
Given the table above, here are some examples of using COUNTIFS in a sheet:
| Formula | Description | Result |
|---|---|---|
| =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.