The COUNTIF function
Counting the cells that meet a criterion: how many orders for the North, how many negative amounts, how many times a code appears.
Syntax
=COUNTIF(range, criteria)
In a French Excel: =NB.SI(plage; critère).
Arguments
- range: the cells to count.
- criteria:
"North","<0",A2,"*urgent*".
An example
=COUNTIF($A$2:$A$500,A2)>1
TRUE on every row whose value appears more than once: copied down, or used in conditional formatting, it flags duplicates.
Pitfalls
- More than 255 characters: COUNTIF does not compare longer texts.
- Wildcards: a text containing * or ? is read as a pattern;
~*for a real asterisk. - Unlocked range for duplicates: without
$, it slides.
Going further
- COUNTIFS.
- Remove duplicates and Near-duplicates: remove the duplicates of a whole file.
Frequently asked questions
- How do I count non-empty cells?
=COUNTA(A:A); with COUNTIF,=COUNTIF(A:A,"<>").