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