ThisCalculator

Everyday

Count Cells Equal to Case Sensitive?

Updated · Standard formula, shown below

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.

Yes, you can count cells that exactly match a specific text value, including case, using the SUMPRODUCT function with the EXACT function in Excel or Google Sheets. The standard COUNTIF function is not case-sensitive, so it will count both “apple” and “Apple” as the same; to differentiate, you need a case-sensitive formula.

Formula

The case-sensitive count formula is:

=SUMPRODUCT(–EXACT(target, range))

Where:

  • target is the text you want to match exactly, including case (e.g., “apple”).
  • range is the range of cells to search (e.g., A1:A10).
  • EXACT compares two text strings and returns TRUE if they are identical, including case, and FALSE otherwise.
  • The double unary (–) converts TRUE/FALSE to 1/0.
  • SUMPRODUCT sums the 1s to give the count.

Worked Example

Suppose you have the following data in cells A1:A10:

A1: apple, A2: Apple, A3: APPLE, A4: apple, A5: banana, A6: Apple, A7: apple, A8: BANANA, A9: Banana, A10: apple

You want to count how many cells contain exactly “apple” (lowercase). Using the formula with target “apple” and range A1:A10:

  1. EXACT(“apple”, A1) → TRUE (because A1 is “apple”)
  2. EXACT(“apple”, A2) → FALSE (A2 is “Apple”)
  3. EXACT(“apple”, A3) → FALSE (A3 is “APPLE”)
  4. EXACT(“apple”, A4) → TRUE
  5. EXACT(“apple”, A5) → FALSE (banana)
  6. EXACT(“apple”, A6) → FALSE (Apple)
  7. EXACT(“apple”, A7) → TRUE
  8. EXACT(“apple”, A8) → FALSE (BANANA)
  9. EXACT(“apple”, A9) → FALSE (Banana)
  10. EXACT(“apple”, A10) → TRUE

The TRUEs are converted to 1s and FALSEs to 0s. Sum = 1+0+0+1+0+0+1+0+0+1 = 4. So the case-sensitive count of “apple” is 4.

How to Use This Formula in Excel or Google Sheets

  1. Enter your data in a column (e.g., A1:A10).
  2. In a blank cell, type: =SUMPRODUCT(--EXACT("apple", A1:A10)) (or reference a cell containing the target).
  3. Press Enter. The result is the number of cells that exactly match “apple” including case.

If you want to count cells that are not equal to a specific case-sensitive value, use: =SUMPRODUCT(--NOT(EXACT("apple", A1:A10))).

Why COUNTIF Is Not Case-Sensitive

The COUNTIF function (and COUNTIFS) ignores case by default. For example, =COUNTIF(A1:A10,"apple") will count all cells that contain “apple” regardless of whether they are “apple”, “Apple”, or “APPLE”. This is because COUNTIF uses a case-insensitive comparison. If you need case-sensitive counting, you must use the EXACT function as shown above.

Common Mistakes and How to Avoid Them

  • Forgetting the double unary (–): Without it, SUMPRODUCT may not sum correctly because TRUE/FALSE are not treated as numbers. Always use -- or multiply by 1.
  • Using COUNTIF instead of SUMPRODUCT: COUNTIF is case-insensitive, so it will give the wrong result for case-sensitive counting.
  • Not locking the range: If you copy the formula down, the range may shift. Use absolute references (e.g., $A$1:$A$10) if needed.
  • Mixing data types: EXACT works with text. If your cells contain numbers, it will compare them as text, which may cause unexpected results. Ensure your data is text or convert it accordingly.
  • Trailing spaces: EXACT is sensitive to spaces. “apple ” (with a space) will not match “apple”. Use TRIM if necessary.

Alternatives for Case-Sensitive Counting

If you prefer not to use SUMPRODUCT, you can use an array formula (in older Excel versions, press Ctrl+Shift+Enter):

=SUM(--EXACT("apple", A1:A10))

In Excel 365 and Google Sheets, you can also use:

=SUM(EXACT("apple", A1:A10)*1)

Or with a helper column: add a column with =EXACT("apple", A1) and then sum that column with =SUM(B1:B10).

Frequently Asked Questions

Can I count cells that are not equal to a case-sensitive value?

Yes, use =SUMPRODUCT(--NOT(EXACT("apple", A1:A10))) to count cells that do not exactly match “apple” (including case).

Does this work for numbers?

EXACT works with numbers as well, but it compares them as text. For example, EXACT(1, “1”) returns TRUE because both are the same text. If you need to compare numbers exactly, you can use the same formula, but be aware that formatting (like decimal places) may affect the result.

How do I count cells that contain a case-sensitive substring?

To count cells containing a specific substring with case sensitivity, use =SUMPRODUCT(--ISNUMBER(FIND("apple", A1:A10))). FIND is case-sensitive, unlike SEARCH.

Why does my formula return 0?

Check that the target text is spelled correctly and that the range is correct. Also, ensure there are no extra spaces in the target or the data. If the range includes empty cells, EXACT will return FALSE for them, which is fine.

Can I use wildcards with EXACT?

No, EXACT does not support wildcards. It only checks for exact equality. If you need wildcard support with case sensitivity, you would need a more complex formula using FIND or a custom function.

Is there a case-sensitive COUNTIF in Excel?

No, there is no built-in case-sensitive COUNTIF. You must use SUMPRODUCT with EXACT or an array formula.

Related

Formula used
in t | Target text | - | apple | 0
in d | Data range (A1:A10) | - | A1:A10 | 0
out count | Case-sensitive count | count | SUMPRODUCT(--EXACT(t, d)) | 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.