Many people are familiar with using the COUNTIF function to count the number of occurrences that meet a specific condition. However, COUNTIF has a limitation: it cannot distinguish between uppercase and lowercase letters. When faced with certain scenarios, it may yield incorrect results. So, how can we modify the formula to obtain the correct answer?
Can COUNTIF Handle This?
The answer is no. COUNTIF cannot differentiate between uppercase and lowercase letters. For COUNTIF, “Abc,” “ABC,” and “aBc” are all considered the same. Therefore, if you apply COUNTIF to the ranges D2, D3, and D4, the result will be 6.
What Formula Can Replace COUNTIF?
The solution is to use the following formula:
=SUMPRODUCT(EXACT(C2,$A$2:$A$7)*1)
Formula Breakdown:
- EXACT Function: This function can distinguish between uppercase and lowercase letters. For example,
="ABC"="Abc"will return FALSE. - EXACT(C2,$A$2:$A$7): This part instructs Excel to verify each cell in the range A2:A7 against C2, resulting in an array of TRUE and FALSE values, such as
{TRUE;FALSE;FALSE;TRUE;TRUE;FALSE}. - *EXACT(C2,$A$2:$A$7)1: By multiplying the array of TRUE and FALSE values by 1, we convert it to numerical values, resulting in
{1;0;0;1;1;0}. - SUMPRODUCT(EXACT(C2,$A$2:$A$7)*1): Finally, this sums up the resulting array, giving the count of how many times “Abc” appears in the range A2:A7, which would be 3 in this case.
Conclusion
By using the combination of the EXACT function and SUMPRODUCT, you can effectively count case-sensitive occurrences in Excel, overcoming the limitations of the COUNTIF function.
A0008
