Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to count table columns in Excel
  • How to create Gauge Chart in Excel
  • How To Load Analysis ToolPak in Excel
  • How to sum a total in multiple Excel tables
  • How to Sort by Color in Excel

References

  • Convert text string to valid reference in Excel using Indirect function
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • Merge tables with VLOOKUP in Excel
  • Approximate match with multiple criteria in Excel
  • How to get relative row numbers in a range in Excel

Data Validations

  • Excel Data validation must contain specific text
  • Excel Data validation allow weekday only
  • Excel Data validation require unique number
  • Excel Data validation don’t exceed total
  • Excel Data validation specific characters only

Count cells equal to case sensitive in Excel

by

This tutorial shows how to Count cells equal to case sensitive in Excel using the example below;

Formula

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

Explanation

To count cells that contain certain text in a case-sensitive manner, you can use a formula that uses the EXACT function along with SUMPRODUCT.

In the example, there is a list of names in a named range (“names”), B3:B9. In the range D3:D6, there is a another list of names, with duplicates removed. To get the count of “ayako”, you can use the formula:

=SUMPRODUCT((--EXACT("ayako",B3:B9)))

In the example, which uses the named range “names”, the first formula is:

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

Both formulas above return 3, since there are 3 occurrences of “ayako” (lowercase) in the range B3:B9.

Worked Example:   Count cells that contain either x or y in Excel

How this formula works

The EXACT function takes to arguments, text1 and text2. When these values match exactly (respecting case), EXACT returns TRUE. In this case, we are giving EXACT a range for text1, so it becomes an array formula and returns an array of TRUE and FALSE values like this:

Worked Example:   Sum top n values with criteria in Excel

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

Each TRUE represents an exact match of “ayako” in B3:B9.

Worked Example:   How to check if cell contains all of many things in Excel

The double-hypen (technically: double unary) converts the TRUE false values into 1’s and 0’s that look like this:

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

Finally, SUMPRODUCT simply adds up the values in the array and returns 3. Because SUMPRODUCT can handle arrays natively, it’s not necessary to use Control+Shift+Enter to enter this formula.

Post navigation

Previous Post:

44 Practical Excel IF function Examples

Next Post:

Large with criteria in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • OR function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel TRUE Function
  • Excel If, Nested If, And/Or Criteria Examples
  • Return blank if in Excel
  • IFNA function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • NOW function: Description, Usage, Syntax, Examples and Explanation
  • Get month from date in Excel
  • How to get Holiday Date from Year in Excel
  • Calculate total hours that fall between two times in Excel
  • How to calculate nth day of year in Excel

Grouping

  • Categorize text with keywords in Excel
  • How to randomly assign data to groups in Excel
  • Running count group by n size in Excel
  • Group times into unequal buckets in Excel
  • Calculate conditional mode with criteria in Excel

General

  • Find Most Frequently Occurring Word in Excel Worksheet
  • How to calculate percent of goal in Excel
  • How to calculate percentage discount in Excel
  • With vs Without Array Formula in Excel
  • How to calculate decrease by percentage in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning