Excel Formulas - Count cells for case sensetive text

Count cells for case sensetive text

Syntax of used function(s)

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

Explanation

To count the number of cells which contains a specific text the EXACT function along with SUMPRODUCT function can used. The range B5:B10 contain the name of employees. The exact name Charls will be searched from this range.

Formula

=SUMPRODUCT(--EXACT("Charls",B5:B10))

EXACT(text1, text2)
The EXACT function can compare two strings.
In the above example the exact name Charls found within the range B5:B10 2 times.

How this formula works

In the above example the EXACT function compares the word Charls with each name within the range B5:B10 and returns True or False. It returns an array like -

[True;False;False;False;False;True;]
Therefore the double-hypen(--) (technically: double unary) converts the True and False with 1 and 0's, and the array like -
[1;0;0;0;0;1]
The SUMPRODUCT then adding up the value in the array and return the result 2.

Count cells for case sensetive text with range name and criteria value

Explanation

To count the number of cells in a range name for a variable that contain a certain text, EXACT function along with SUMPRODUCT function can be used.

Formula

=SUMPRODUCT(--EXACT(D7,Employee))

How this formula works:

In the above example the EXACT function compares the word in the criteria variable D7 with each name within the range name Employee(B5:B10) and returns True or False. It returns an array like -

[False;False;False;True;False;False;]
Therefore the double-hypen(--) (technically: double unary) converts the True and False with 1 and 0's, and the array like -
[0;0;0;1;0;0;]
The SUMPRODUCT then adding up the value in the array and return the result 1.

Previous: Excel Formulas - Count number of cells between two dates
Next: Excel Formulas - Count cells which are equal to each value of a given range