{"id":1533,"date":"2021-07-04T12:21:25","date_gmt":"2021-07-04T04:21:25","guid":{"rendered":"https:\/\/www.fyndpro.com\/?p=1533"},"modified":"2024-09-17T12:20:32","modified_gmt":"2024-09-17T04:20:32","slug":"excel-solve-case-sensitive","status":"publish","type":"post","link":"https:\/\/www.fyndpro.com\/en\/excel-solve-case-sensitive\/","title":{"rendered":"Solving the Case-Sensitivity Issue with COUNTIF in Excel"},"content":{"rendered":"

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?<\/p>\n

\"\"<\/p>\n

Can COUNTIF Handle This?<\/h4>\n

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.<\/p>\n

What Formula Can Replace COUNTIF?<\/h4>\n

The solution is to use the following formula:<\/p>\n

\n
\n
\n

=SUMPRODUCT(EXACT(C2,$A$2:$A$7)*1)<\/em><\/p>\n

\"\"<\/p>\n<\/div>\n<\/div>\n<\/div>\n

Formula Breakdown:<\/h4>\n
    \n
  • EXACT Function<\/strong>: This function can distinguish between uppercase and lowercase letters. For example,\u00a0=\"ABC\"=\"Abc\"<\/code>\u00a0will return FALSE.<\/li>\n
  • EXACT(C2,$A$2:$A$7)<\/strong>: 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\u00a0{TRUE;FALSE;FALSE;TRUE;TRUE;FALSE}<\/code>.<\/li>\n
  • *EXACT(C2,$A$2:$A$7)1<\/em>: By multiplying the array of TRUE and FALSE values by 1, we convert it to numerical values, resulting in\u00a0{1;0;0;1;1;0}<\/code>.<\/li>\n
  • SUMPRODUCT(EXACT(C2,$A$2:$A$7)*1)<\/strong>: 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.<\/li>\n<\/ul>\n

    Conclusion<\/h4>\n

    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.<\/p>\n

    A0008<\/em><\/p>","protected":false},"excerpt":{"rendered":"

    Many people are familiar with using the COUNTIF functio […]<\/p>\n","protected":false},"author":1,"featured_media":2445,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[8,7,12,20],"tags":[125,180,126],"class_list":["post-1533","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel","category-excel-formulas","category-mathematical","category-onlinesupport","tag-countif","tag-exact","tag-sumproduct"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2024\/04\/Excel-new9.png?fit=2102%2C679&ssl=1","_links":{"self":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts\/1533","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/comments?post=1533"}],"version-history":[{"count":7,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts\/1533\/revisions"}],"predecessor-version":[{"id":2928,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts\/1533\/revisions\/2928"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/media\/2445"}],"wp:attachment":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/media?parent=1533"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/categories?post=1533"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/tags?post=1533"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}