=SUMPRODUCT(COUNTIF(rng,things))
If you need to count the number of cells that contain values not equal to a particular value, you can use the COUNTIF function inside of SUMPRODUCT. In the generic form of the formula (above) rng represents a range of cells, and things represents the values to count.
In the example, cell G5 contains this formula:
=SUMPRODUCT(COUNTIF(B5:B10,things))How the formula works:
COUNTIF counts the number of cells in the range that meet criteria you supply. When you give COUNTIF a range of cells as the criteria, it returns an array of numbers a result. Each item in this array represents the count of one thing in the range. Note that the criteria tests for equality, but is not case-sensitive.
Finally, the SUMPRODUCT function simply sums the items in the array and returns the total.
