Excel countif
Note that the COUNTBLANK function in Excel gives the same result and is just as easy to use. where the criteria consist of a pair of empty double quotes. Count blank cellsĪpplying the above principles, we can also determine the number of blank cells within a range with the format. Read more about mixed cell references here. In the above example, mixed cell references are used ($E2:$E9) to make it easier when copying the formula across from cell J2 to M2. There are no quotation marks around the cell reference since “E2” is not the literal value being extracted from the cell range. The following example counts the number of cells greater than the value in cell J1. When using logical operators with cell reference criteria, the logical operator itself is enclosed within double quotation marks, followed by an ampersand (&) to concatenate or join the operator to the relevant cell reference. Note that both the logical operator > (greater than) and the numeric value are enclosed in double quotes. The following combinations of these operators are also used for even more specific comparisons.īelow is the format to apply the COUNTIF function to determine the number of customers with more than one year of membership. 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 ) The COUNTIF function has only two arguments (range and criteria).