COUNTIF returns 9, since there are nine cells in D5:D16 that are not equal to “red”.
Not equal to
In Excel, the operator for not equal to is “<>”. For example:
COUNTIF function
The COUNTIF function counts the number of cells in a range that meet supplied criteria: To use the not equal to operator (<>) in COUNTIF, it must be enclosed in double quotes like this: This is a requirement of COUNTIF, which is in a group of eight functions that share this syntax. In example shown, we want to count cells not equal to “red” in D5:D16, so we use “<>red” for criteria. The formula in G5 is: In cell G6, we count all cells not equal to blue with a similar formula: Note: COUNTIF is not case-sensitive. The word “red” can appear in any combination of uppercase / lowercase letters.
Not equal to another cell
To use a value in another cell as part of the criteria, use the ampersand (&) operator to concatenate like this: For example, if A1 contains 100 the criteria will be “<>100” after concatenation, and COUNTIF will count cells not equal to 100:
COUNTIFS function
The COUNTIFs function is designed to handle multiple criteria, but can be used just like the COUNTIF function in this example: Video: How to use the COUNTIFS function
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
