The COUNTIF function in Excel counts the number of cells in a range that match one supplied condition. Criteria can include logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Criteria can also be based on a value from another cell, as explained below. Show COUNTIF is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and COUNTIF requires a cell range, you can't use an array. COUNTIF only supports a single condition. If you need to apply multiple criteria, use the COUNTIFS function. If you need to manipulate values in the range argument as part of a logical test, see the SUMPRODUCT and/or FILTER functions. Basic exampleIn the worksheet shown above, the following formulas are used in cells G5, G6, and G7: =COUNTIF(D5:D12,">100") // count sales over 100 =COUNTIF(B5:B12,"jim") // count name = "jim" =COUNTIF(C5:C12,"ca") // count state = "ca" Notice COUNTIF is not case-sensitive, "CA" and "ca" are treated the same. Double quotes ("") in criteriaIn general, text values need to be enclosed in double quotes (""), and numbers do not. However, when a logical operator is included with a number, the number and operator must be enclosed in quotes, as seen in the second example below: =COUNTIF(A1:A10,100) // count cells equal to 100 =COUNTIF(A1:A10,">32") // count cells greater than 32 =COUNTIF(A1:A10,"jim") // count cells equal to "jim" Value from another cellA value from another cell can be included in criteria using concatenation. In the example below, COUNTIF will return the count of values in A1:A10 that are less than the value in cell B1. Notice the less than operator (which is text) is enclosed in quotes. =COUNTIF(A1:A10,"<"&B1) // count cells less than B1 Not equal toTo construct "not equal to" criteria, use the "<>" operator surrounded by double quotes (""). For example, the formula below will count cells not equal to "red" in the range A1:A10: =COUNTIF(A1:A10,"<>red") // not "red" Blank cellsCOUNTIF can count cells that are blank or not blank. The formulas below count blank and not blank cells in the range A1:A10: DatesThe easiest way to use COUNTIF with dates is to refer to a valid date in another cell with a cell reference. For example, to count cells in A1:A10 that contain a date greater than the date in B1, you can use a formula like this: =COUNTIF(A1:A10, ">"&B1) // count dates greater than A1 Notice we must concatenate an operator to the date in B1. To use more advanced date criteria (i.e. all dates in a given month, or all dates between two dates) you'll want to switch to the COUNTIFS function, which can handle multiple criteria. The safest way to hardcode a date into COUNTIF is to use the DATE function. This ensures Excel will understand the date. To count cells in A1:A10 that contain a date less than April 1, 2020, you can use a formula like this =COUNTIF(A1:A10,"<"&DATE(2020,4,1)) // dates less than 1-Apr-2020 WildcardsThe wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in criteria. A question mark (?) matches any one character and an asterisk (*) matches zero or more characters of any kind. For example, to count cells in A1:A5 that contain the text "apple" anywhere, you can use a formula like this: =COUNTIF(A1:A5,"*apple*") // cells that contain "apple" To count cells in A1:A5 that contain any 3 text characters, you can use: =COUNTIF(A1:A5,"???") // cells that contain any 3 characters The tilde (~) is an escape character to match literal wildcards. For example, to count a literal question mark (?), asterisk(*), or tilde (~), add a tilde in front of the wildcard (i.e. ~?, ~*, ~~). OR logicThe COUNTIF function is designed to apply just one condition. However, to count cells that contain "this OR that", you can use an array constant and the SUM function like this: =SUM(COUNTIF(range,{"red","blue"})) // red or blue The formula above will count cells in range that contain "red" or "blue". Essentially, COUNTIF returns two counts in an array (one for "red" and one for "blue") and the SUM function returns the sum. For more information, see this example. Notes
Which function counts cells that meet multiple conditions?The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met.
What is Countif function in Excel?Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.
Which function is used to count the number of cells?The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers.
Can I use Countif with multiple criteria?Using COUNTIF with multiple criteria. The best function you can use for situations when you need to count cells based on multiple criteria is the COUNTIFS function. The “s” on the end of Countif makes it plural, and that means that there are multiple criteria.
|