Excel Formulas - Count cells which are not equal to each value of a given range

Count cells which are not equal to each value of a given range

Syntax of used function(s)

COUNTA(value1, [value2], ...)
SUMPRODUCT(array1, [array2], [array3], ...)

Explanation

To count number of items within a range which values are not equal to the each value of a specified range, the SUMPRODUCT function have been used along with the COUNTA function.

Formula

=COUNTIF(C4:C10,">70")

How the formula works

In the above example COUNTA and SUMPRODUCT function have been used. The COUNTA function counts the number of cells that are not empty within a range.
The SUMPRODUCT function search each value of the range named as "Working" with the range C5:C10. Therefore subtract the result of SUMPRODUCT function
from the result of COUNTA function. The COUNTA function returns the result 6 and SUMPRODUCT function returns the result 5. Then returns the formula result 1.

Previous: Excel Formulas - Count cells using greater than operator
Next: Excel Formulas - Count number of cells begins with string