However, the criteria argument defaults to “is equal to” - therefore there is no need to enter the equal sign (=) when stating the “is equal to” criterion. The COUNTIF formula makes use of the standard logical mathematical operators (=, >, <). will return the number of occurrences of the number “2” in the range E2 to E9. Likewise, when using numeric criteria within a COUNTIF formula, no double quotation marks are required.
No double quotation marks are used because “I2” is not the literal value being searched for within the range. In the example below, the criteria refer to the value in cell I2 (Basic).
We can also point to a cell that has the criteria value by using a cell reference. Text criteria are not case sensitive therefore, using the criteria of “Basic”, “basic” or “BASIC” would yield the same results.Text criteria must be entered within double quotation marks.In the following dataset, we can quickly count the number of customers with the membership level “Basic” in column D by using the formula: = COUNTIF ( D2 : D9, “basic” ) Range: the group of cells to be evaluated.Ĭriteria: the text, number, or expression that cells in the range must satisfy in order to be counted. Both arguments are required, and the syntax is as follows: = COUNTIF (range, criteria ) SUMIF: Returns a conditional sum across a range.ĭCOUNTA: Counts values, including text, selected from a database table-like array or range using a SQL-like query.ĭCOUNT: Counts numeric values selected from a database table-like array or range using a SQL-like query.ĬOUNTUNIQUE: Counts the number of unique values in a list of specified values and ranges.ĬOUNTA: Returns the number of values in a dataset.ĬOUNTBLANK: Returns the number of empty cells in a given range.ĬOUNT: Returns the number of numeric values in a dataset.The COUNTIF function has only two arguments (range and criteria). To use multiple criteria, use COUNTIFS or the database functions DCOUNT or DCOUNTA.ĬOUNTIFS: Returns the count of a range depending on multiple criteria. COUNTIF can only perform conditional counts with a single 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. If a number is provided, each cell in range is checked for equality with criterion. If range contains numbers to check against, criterion may be either a string or a number. Each cell in range is then checked against criterion for equality (or match, if wildcards are used).
A string criterion must be enclosed in quotation marks. To match an actual question mark or asterisk, prefix the character with the tilde ( ~) character (i.e. criterion can contain wildcards including ? to match any single character or * to match zero or more contiguous characters. If range contains text to check against, criterion must be a string. Range - The range that is tested against criterion.Ĭriterion - The pattern or test to apply to range. To get an example spreadsheet and follow along with the video, click “Make a Copy” below. Returns a conditional count across a range.