COUNTIF FUNCTION

Understanding COUNTIF Formula

COUNTIF is a function for calculating the number of numerical cells that meet a certain criteria. COUNTIF can be used to count cells with dates, numbers, or text in them. COUNTIF’s criteria support logical operators (>,,>,=) and wildcards (*,?). We give a certain condition to find the number of the cells in a range. Let us look at syntax and example to have better understanding.

Syntax of COUNTIF formula

The syntax of the COUNTIF function is as follows:

=COUNTIF (range, criteria)

The first argument(range) specifies the range to which criteria should be applied.

The second argument(criteria) specifies the criteria.

Let us look at example for better understanding

Example for COUNTIF Function

=COUNTIF(C4:C10,">400") // count sales over 400
=COUNTIF(B4:B10,">=2004") // count years greater than or equal to 2004

Only one condition is supported by COUNTIF. Use the COUNTIFS function if you need to apply multiple criteria. To Know how to use COUNTIFS function click here.

Using Double Quotes (“”) in criteria

Text values, in general, must be enclosed in double quotes (“”), whereas numbers do not. When a number is used with a logical operator, the number and operator must be enclosed in quotes, as shown below:

=COUNTIF(C1:C10,400) // count cells equal to 400
=COUNTIF(A1:A10,">32") // count cells greater than 32
=COUNTIF(A1:A10,"andrew") // count cells contains "andrew"

Getting values from another cell

Concatenation can be used to include a value from another cell in criteria. COUNTIF will return the count of values in B4:B10 that are less than the value in cell C6 in the example below.

=COUNTIF(B4:B10,"<"&C6) // count cells less than C6

Using Not equal to criteria

Use the “<>” operator between double quotes to create “not equal to” criteria (“”). For example, the formula below will count cells in the range C4:C10 that are not equal to “300.”

=COUNTIF(C4:C10,"<>300") // not "300"

To Count Blank cells

COUNTIF can count cells that are blank or not blank. The formulas below count blank and not blank cells in the range A1:A10:

=COUNTIF(C1:C10,"<>") // not blank
=COUNTIF(C1:C10,"") // blank

Dates

The simplest way to use COUNTIF with dates is to use a cell reference to a valid date in another cell. For example,

=COUNTIF(C1:C10, ">"&D1) // count dates greater than D1

It’s important to remember that the date in D1 must be concatenated with an operator. You’ll want to use the COUNTIFS function, which can handle multiple criteria, if you want to use more advanced date criteria (for example, all dates in a given month or all dates between two dates).

=COUNTIF(A1:A10,"<"&DATE(2001,3,2)) // dates less than 2-Mar-2001

Wildcards

In criteria, the wildcard characters question mark (? ), asterisk (), and tilde () can be used. An asterisk () matches zero or more characters of any kind, while a question mark (?) matches any single character. For example:

=COUNTIF(D1:D5,"*canada*") // cells that contain "Canada"

To count cells in A1:A5 that contain any 2 text characters, you can use:

=COUNTIF(A1:A5,"??") // cells that contain any 2 characters

OR logic

The 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.

Practice COUNTIF function below:

The formula can be entered in the highlighted cell. If the formula entered is correct, it shows good job. If the formula is entered incorrectly, the result will be invalid. Please keep in mind that you can only enter formula in the highlighted cells.

Hurray! You know now how to use COUNTIF function. See how COUNT and COUNTIFS function works and practice it.