Count Cells That Contain Case Sensitive
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
This article explains how to count cells in Excel or Google Sheets that contain specific text while respecting uppercase and lowercase differences. You enter your data range and the text you are looking for, and the formulas below return the exact count of cells where the case matches. Standard COUNTIF functions ignore case, so the methods shown here use SUMPRODUCT, FIND, or EXACT to force case-sensitive matching.
Formula
The core formula for counting cells that contain a specific text string with case sensitivity is:
=SUMPRODUCT(–ISNUMBER(FIND(search_text, range)))
In words: the FIND function looks for the search text in each cell and returns its starting position if found, or an error if not found. ISNUMBER converts each result to TRUE (if found) or FALSE (if not). The double negative (–) changes TRUE/FALSE to 1/0, and SUMPRODUCT adds them up, giving the total count of cells that contain the text with the exact case.
Variables:
- range: the cells you want to check (e.g., A1:A10).
- search_text: the text you are looking for, exactly as it should appear (e.g., “apple”).
Worked Example
Using the default values from the calculator: range = A1:A10, search text = “apple”. Suppose the cells contain: Apple, apple, APPLE, pineapple, apple pie, Green apple, crabapple, Apple, apple, APPLE.
Step 1: Apply FIND(“apple”, A1:A10). It returns 1 for cells with lowercase “apple” at the start (A2, A5, A9, A10), a number for cells where “apple” appears later (A6, A7), and an error for cells with uppercase or different case (A1, A3, A4, A8).
Step 2: ISNUMBER turns the numbers into TRUE and errors into FALSE.
Step 3: The double negative converts TRUE to 1 and FALSE to 0.
Step 4: SUMPRODUCT adds: 1 (A2) + 1 (A5) + 1 (A6) + 1 (A7) + 1 (A9) + 1 (A10) = 6.
So the case-sensitive count of cells containing “apple” is 6. The calculator returns 6, matching this result.
Why Standard COUNTIF Fails
Excel’s COUNTIF and COUNTIFS functions are not case-sensitive. For example, =COUNTIF(A1:A10,”apple”) counts cells with “apple”, “Apple”, “APPLE”, and any other case variation. This is by design for general counting, but it becomes a problem when you need to distinguish between product codes, usernames, or file names where case matters. The FIND-based approach is the standard workaround.
Alternative Methods for Case-Sensitive Counting
Besides SUMPRODUCT with FIND, there are other ways to achieve the same result:
- SUMPRODUCT with EXACT: =SUMPRODUCT(–EXACT(search_text, range)) counts cells that are exactly equal to the search text, but it does not count cells that contain the text as part of a longer string. Use this when you want whole-cell matches only.
- Array formula with FIND: In older Excel versions, you might use =SUM(–ISNUMBER(FIND(search_text, range))) entered with Ctrl+Shift+Enter. In modern Excel, this works without the special entry.
- FILTER function (Excel 365): =ROWS(FILTER(range, ISNUMBER(FIND(search_text, range)))) returns the count by filtering the range first.
Common Mistakes and How to Avoid Them
- Forgetting the double negative: Without –, SUMPRODUCT treats TRUE/FALSE as 0, so the result is always 0. Always include — or multiply by 1.
- Mixing up FIND and SEARCH: SEARCH is not case-sensitive; FIND is. Use FIND for case-sensitive matching.
- Using full-column references: =SUMPRODUCT(–ISNUMBER(FIND(“apple”, A:A))) works but may slow down the workbook because it processes all 1,048,576 rows. Use a specific range like A1:A1000 instead.
- Not accounting for empty cells: FIND returns an error for empty cells, but ISNUMBER handles that correctly, so no issue.
When to Use Case-Sensitive Counting
Case-sensitive counting is useful in real-world scenarios where case distinguishes meaning:
- Product codes where “abc” and “ABC” are different items.
- User IDs or email addresses where the local part is case-sensitive.
- File names on Linux servers where case matters.
- Password lists or security audits.
In most other contexts, case-insensitive counting is fine and simpler with COUNTIF.
Limitations and Performance
The SUMPRODUCT method is accurate but can be slow on very large datasets because it evaluates each cell individually. If you have hundreds of thousands of rows, consider using a helper column with a formula like =ISNUMBER(FIND(“apple”, A1)) and then sum that column. Also, the formula counts cells that contain the text anywhere, not just at the start. For whole-word or whole-cell matches, use EXACT or add word boundaries if needed.
Frequently Asked Questions
How do I count cells that contain a case-sensitive substring in Google Sheets?
Use the same SUMPRODUCT and FIND formula in Google Sheets. It works identically. For example, =SUMPRODUCT(–ISNUMBER(FIND(“apple”, A1:A10))).
Can I count case-sensitive cells that match exactly, not just contain?
Yes, use =SUMPRODUCT(–EXACT(“apple”, A1:A10)). EXACT returns TRUE only if the entire cell content is identical, including case.
Why does my formula return 0 even though I see the text?
Check for leading/trailing spaces in the cells or the search text. Also, ensure you are using FIND, not SEARCH. If the text is in a different case, FIND will not find it.
Is there a way to make COUNTIF case-sensitive?
No, COUNTIF is inherently case-insensitive. You must use SUMPRODUCT, EXACT, or an array formula. There is no built-in option to change COUNTIF’s behavior.
How do I count case-sensitive cells that do NOT contain the text?
Use =SUMPRODUCT(–ISNUMBER(FIND(“apple”, A1:A10))=FALSE) or =COUNTA(A1:A10)-SUMPRODUCT(–ISNUMBER(FIND(“apple”, A1:A10))). The second formula is simpler and also ignores empty cells.
Does this work with numbers or only text?
FIND works with numbers as well, but numbers are not case-sensitive. The formula will treat numeric values as text and match them, but case sensitivity is irrelevant for digits.
Related
Formula used
in r | Range | - | A1:A10 | 0 in s | Search text | - | apple | 0 out count | Case-sensitive count | - | SUMPRODUCT(--ISNUMBER(FIND(s,r))) | 0
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.