Excel Formulas - Count cells for case sensetive text
Count cells for case sensetive text
![Count cells for case sensetive text](https://www.w3resource.com/w3r_images/count-cells-for-case-sensitive-text-1.png)
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
![Count cells for case sensetive text range name and criteria value](https://www.w3resource.com/w3r_images/count-cells-for-case-sensitive-text-2.png)
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics