Excel Formulas - Count cells which are not equal either one or another cirteria

Count cells which are not equal either one or another cirteria

Syntax of used function(s)

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

The SUMPRODUCT function is used to multiplies the corresponding components in the given arrays, and returns the sum of those products.

Explanation

To count number of cells from a range which are not equal to one or other, mentioned in the criteria, COUNTIFS function can be used. In this example a list of department shown in column C where the employees are working. There are 6 cells in the department column, some are duplicates.
To count the number of department which are not "HR" or "MKT".

Formula

=COUNTIFS(C5:C10,"<>HR",C5:C10,"<>MKT")

How this formula works

The COUNTIFS function count cells that meet one or more criteria. The each criteria must search the range from C5:C10. The not equal to operator excludes the department "HR" and "MKT"

Count cells with not equal operator using SUMPRODUCT function

Syntax of used function(s)

SUMPRODUCT(array1, [array2], [array3], ...)

Explanation

To count number of cells from a range which are not equal to one or other in given criteria, SUMPRODUCT function can be used.

Formula

=SUMPRODUCT((C5:C10<>"HR")*(C5:C10<>"MKT"))

How this formula works

The SUMPRODUCT function count cells that meet one or more criteria. The each criteria must search the range from C5:C10. The not equal to operator excludes the department "HR" and "MKT"

Previous: Excel Formulas - Count long number of more than 15 digits not using COUNTIF()
Next: Excel Formulas - Count cells that contain any of the specified string