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

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

**Syntax of used function(s)**

SUMPRODUCT(array1, [array2], [array3], ...) COUNTIF(criteria_range, criteria)

**Explanation**

To count number of cells which are equal to one of many values the COUNTIF function inside the SUMPRODUCT function can be used.

**Formula**

```
=SUMPRODUCT(COUNTIF(C5:C10,D5:D6))
```

**How the formula works**

In the above example COUNTIF search the employees working in the department IT or HR and counts them. It returns an array of number as results if matches the criteria. It looks like -

```
=SUMPRODUCT({1;1;1;1;1})
Therefore the SUMPRODUCT function sums up the items in the array and return the result.
```

## Count cells which are equal to each value of a given array constant

**Formula**

```
=SUM(COUNTIF(Department,{"HR","MKT"}))
```

**How the formula works**

In the above example COUNTIF search the employees working in the department "HR" or "MKT" and counts them. A range name "Department" instead of range C5:C10 have been used and for criteria an array {"HR","MKT"} have been used.

**Previous: ** Excel Formulas - Count cells for case sensetive text

**Next:**
Excel Formulas - Count cells using not equal to operator

**Weekly Trends and Language Statistics**- Weekly Trends and Language Statistics