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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics