Count cells greater than

In this example, the goal is to count test scores in column C that are greater than 90. The simplest way to do this is with the COUNTIF function, which takes two arguments, range and criteria:

=COUNTIF(range,criteria)

All test scores are in the range C5:C16 and we want to count scores greater than 90, so we configure COUNTIF like this:

=COUNTIF(C5:C16,">90") // returns 2

COUNTIF returns 2, since there are two scores in C5:C16 that are greater than 90. Notice that criteria is given as a text value in double quotes (""). This is a requirement of the COUNTIF function which is in a group of eight functions that use a special syntax for criteria. In this syntax, logical operators are joined with numeric values and provided as text.

Greater than or equal to

To count cells that are greater than or equal to, adjust the formula to use ">=" instead of ">". In the example shown, the formula in F6 is:

=COUNTIF(C5:C16,">=90") // returns 3

Here, COUNTIF returns 3, since there are three scores in C5:C16 greater than or equal to 90.

Value in another cell

To adjust the formula to use a value in another cell as part of the criteria, you can concatenate the logical operator to the cell reference with the ampersand (&) operator like this:

=COUNTIF(range,">"&A1)

For example, with 90 in cell A1, the criteria will become ">90" after concatenation:

=COUNTIF(range,">"&A1)
=COUNTIF(range,">90")
=2

The result will again be 2. If the value in A1 is changed to a different number, COUNTIF will return a new result.

COUNTIFS function

The COUNTIFS function is designed to handle multiple criteria but can be used just like the COUNTIF function in this example:

=COUNTIFS(C5:C16,">90") // returns 2
=COUNTIFS(C5:C16,">=90") // returns 3

Video: How to use the COUNTIFS function