# 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"

﻿