Explanation
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
Explanation
In this example, the goal is to count test scores in column C that are less than 75. The simplest way to do this is with the COUNTIF function , which takes two arguments , range and criteria :
=COUNTIF(range,criteria)
The test scores in the range C5:C16 and we want to count scores less than 75 , so we configure COUNTIF like this:
=COUNTIF(C5:C16,"<75") // returns 1
Since there is only one score in C5:C16 that is less than 75, COUNTIF returns 1 as a result. 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.
Less than or equal to
To count cells that are less than or equal to , adjust the formula to use “<=” instead of “<”. In the example shown, the formula in F6 is:
=COUNTIF(C5:C16,"<=75") // returns 2
Here, COUNTIF returns 2 since there are two scores in C5:C16 less than or equal to 75.
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 75 in cell A1, the criteria will be “<=75” after concatenation:
=COUNTIF(range,"<="&A1)
=COUNTIF(range,"<=75")
=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,"<75") // returns 1
=COUNTIFS(C5:C16,"<=75") // returns 2
Video: How to use the COUNTIFS function
