COUNTIF


Counts the number of cells in a range that meet a specified condition.

Syntax:

COUNTIF(test_range, condition)
test_range is the range to be tested. condition may be:
  • a number, such as 34.5
  • an expression, such as 2/3 or SQRT(B5)
  • a text string

COUNTIF counts those cells in test_range that are equal to condition, unless condition is a text string that starts with a comparator:
>, <, >=, <=, =, <>

In this case COUNTIF compares those cells in test_range with the remainder of the text string (interpreted as a number if possible or text otherwise).

For example the condition “>4.5” tests if the content of each cell is greater than the number 4.5, and the condition “<dog” tests if the content of each cell would come alphabetically before the text dog.

Blank (empty) cells in test_range are ignored (they never satisfy the condition). condition can only specify one single condition.

Example:

COUNTIF(C2:C8, ">=20")
returns the number of cells in C2:C8 whose contents are numerically greater than or equal to 20.
COUNTIF(C2:C8, F1)
where F1 contains the text >=20, returns the same number.
COUNTIF(C2:C8, "<"&F2)
where F2 contains 20 returns the number of cells in C2:C8 whose contents are numerically less than 20. (Advanced topic: this works because the & operator converts the content of F2 to text, and concatenates it with "<"; COUNTIF then converts it back to a number).
COUNTIF(A2:A8, ">=P")
returns the number of cells in A2:A8 whose contents begin with the letter P or later in the alphabet.
COUNTIF(B2:B8, "red")
returns the number of cells in B2:B8 containing red, but this number may depend on the option settings discussed above.
Advanced topic:
COUNTIF(B2:B8, ".+")
returns the number of cells in B2:B8 containing one or more character, e.g. not blank, using the syntax of regular expressions.
SUMPRODUCT(B2:B8="Red").
returns the number of cells in B2:B8 matching Red, with case sensitivity.






This page is protected by Google reCAPTCHA. Privacy - Terms.
 
Built using Zapof