Excel formula for Beginners – How to Count cells equal to case sensitive

Hits: 12

(Excel examples for Beginners)

In this end-to-end excel example, you will learn – How to Count cells equal to case sensitive.

 

How to Count cells equal to case sensitive

Generic formula

=SUMPRODUCT((--EXACT(value,range)))

Explanation

To count cells that contain specific text, taking into account upper and lower case, you can use a formula based on the EXACT function together with the SUMPRODUCT function. In the example shown, E5 contains this formula, copied down:

=SUMPRODUCT((--EXACT(D5,names)))

Where “names” is the named range B5:B11. This provides a case-sensitive count of each name listed in column D.

How this formula works

The EXACT function takes two arguments, text1 and text2. When text1 and text2 match exactly (respecting case), EXACT returns TRUE. Otherwise, EXACT returns FALSE:

=EXACT("ayako","ayako") // returns TRUE
=EXACT("ayako","Ayako") // returns FALSE

In the example shown, we need check all values in column B each time we count a name in column D. Because we are giving EXACT multiple values, it returns multiple results. In E5, EXACT returns:

{FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE}

Each TRUE represents an exact match of “ayako” in B5:B11. Because we want to count results, we use a double-negative (–) to convert TRUE and FALSE values into 1’s and 0’s. The resulting array is:

{0;0;0;0;1;1;1}

Finally, SUMPRODUCT sums the values in the array and returns 3.

Note: Because SUMPRODUCT can handle arrays natively, it’s not necessary to use Control+Shift+Enter to enter this formula.

 

Personal Career & Learning Guide for Data Analyst, Data Engineer and Data Scientist

Applied Machine Learning & Data Science Projects and Coding Recipes for Beginners

A list of FREE programming examples together with eTutorials & eBooks @ SETScholars

95% Discount on “Projects & Recipes, tutorials, ebooks”

Projects and Coding Recipes, eTutorials and eBooks: The best All-in-One resources for Data Analyst, Data Scientist, Machine Learning Engineer and Software Developer

Topics included: Classification, Clustering, Regression, Forecasting, Algorithms, Data Structures, Data Analytics & Data Science, Deep Learning, Machine Learning, Programming Languages and Software Tools & Packages.
(Discount is valid for limited time only)

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.

Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners

Please do not waste your valuable time by watching videos, rather use end-to-end (Python and R) recipes from Professional Data Scientists to practice coding, and land the most demandable jobs in the fields of Predictive analytics & AI (Machine Learning and Data Science).

The objective is to guide the developers & analysts to “Learn how to Code” for Applied AI using end-to-end coding solutions, and unlock the world of opportunities!

 

Statistics for Beginners in Excel – Fisher’s Exact Test

Python Example – Write a Python program to convert true to 1 and false to 0

Excel formula for Beginners – How to Filter with exact match in Excel