Returns a conditional count across a range. COUNTIF Function To get an example spreadsheet and follow along with the video, click “Make a Copy” below. Make a copy COUNTIF(A1:A10,">20") COUNTIF(A1:A10,"Paid") COUNTIF(range, criterion) range - The range that is tested against criterion. criterion - The pattern or test to apply to range. If range contains text to check against, criterion must be a string. criterion can contain wildcards including ?
to match any single character or * to match zero or more contiguous characters. To match an actual question mark or asterisk, prefix the character with the tilde (~) character (i.e. ~? and ~*). A string criterion must be enclosed in quotation marks. Each cell in range is then checked against criterion for equality (or match, if wildcards are used). If range contains numbers to check against, criterion may be either a string or a number. If a number is
provided, each cell in range is checked for equality with criterion. Otherwise, criterion may be a string containing a number (which also checks for equality), or a number prefixed with any of the following operators: =, >, >=, <, or <=, which check whether the range cell is equal to, greater than, greater than or equal to, less than, or less than or equal to the criterion value, respectively. COUNTIF is not case sensitive. COUNTIFS: Returns the count of a
range depending on multiple criteria. SUMIF: Returns a conditional sum across a range.Sample Usage
Syntax
Notes
Examples
Make a copy
See Also
DCOUNTA: Counts values, including text, selected from a database table-like array or range using a SQL-like query.
DCOUNT: Counts numeric values selected from a database table-like array or range using a SQL-like query.
COUNTUNIQUE: Counts the number of unique values in a list of specified values and ranges.
COUNTA: Returns the number of values in a dataset.
COUNTBLANK: Returns the number of empty cells in a given range.
COUNT: Returns the number of numeric values in a dataset.
Was this helpful?
How can we improve it?
Counts the number of cells within a range that meet a criterion. Sample Usage COUNTIF(Quantity:Quantity, >25) Syntax COUNTIF( range criterion range — The group of cells to count. criterion — The value that determines which cells in the range will be counted; for example: 15, "Hello World!", or >25.
)
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 =COUNTIF(Item:Item, <> "Shirt") won't count any blank cells that exist in the Item column.
Examples
This example references the following sheet information:
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 COUNTIF in a sheet:
=COUNTIF([Units Sold]:[Units Sold], >50) | Count the number of rows where the value in the “Units Sold” column is greater than 50. Rows 1 & 3 meet the criteria. | 2 |
=COUNTIF([In Stock?]:[In Stock?], 1) | Count the number of rows where the “In Stock?” checkbox is checked. Rows 1 & 3 meet the criteria. | 2 |
=COUNTIF([Clothing Item]:[Clothing Item], "Jacket") | Count the number of rows where value in the “Clothing Item” column is “Jacket.” Row 3 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