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

Data Analysis

  • Everything about Charts in Excel
  • Data Series in Excel
  • How To Filter Data in Excel
  • How to calculate average last N values in a table in Excel
  • How to create dynamic reference table name in Excel

References

  • How to use Excel OFFSET function
  • How to use Excel FORMULATEXT function
  • Offset in Excel
  • Vlookup Examples in Excel
  • Excel Advanced Lookup using Index and Match Functions

Data Validations

  • Excel Data validation unique values only
  • How To Create Drop-down List in Excel
  • Excel Data validation allow uppercase only
  • Excel Data validation whole percentage only
  • Excel Data validation don’t exceed total

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.

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:

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

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

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:

How to use Excel CHOOSE Function

Next Post:

Customize Ribbon 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

  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • How to use IFS function in Excel
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • Invoice status with nested if in Excel
  • Excel If, Nested If, And/Or Criteria Examples

Date Time

  • How to calculate next anniversary date or birthday in Excel
  • Get week number from date in Excel
  • TIMEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • Generate series of dates by weekends in Excel
  • WEEKNUM function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • If cell contains one of many things in Excel
  • Categorize text with keywords in Excel
  • Group arbitrary text values in Excel
  • How to randomly assign data to groups in Excel
  • Map inputs to arbitrary values in Excel

General

  • Share Excel data with Word documents
  • 231 Keyboard Shortcut Keys In Excel
  • How to calculate percent of goal in Excel
  • Freeze and Unfreeze Panes in Excel
  • Excel Autofill Cell Ranges, Copy, Paste
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning