=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.