Hello everybody,

I'm not an Excel or VBA professional, but I'm using it quite a bit for the last 10 years.
Today I ran into an issue I (think) I've never encountered before.

Before anybody complains, the following text has been written by ChatGPT based on all the information I collected in the last 2 hours.

I ran into a serious, hard-to-detect issue in Excel where the conditional aggregation functions (SUMIF/SUMIFS, COUNTIF/COUNTIFS, and likely the rest of the family such as AVERAGEIF/S) can return incorrect results when the criteria text contains an “E” pattern that resembles scientific notation (e.g., 00E2, 10359E2, 01E2, etc.).

Core problem

When a criterion looks like scientific notation, Excel appears to coerce the text criterion into a numeric interpretation during evaluation, which can cause unrelated text values to match and be included in sums/counts. This can happen even if the data column is formatted as Text and the values are intended to be treated as literal strings.

A classic example: a COUNTIF search for a serial like 10359E2 can incorrectly count values like 1035900 as matches, because Excel effectively interprets 10359E2 as a scientific-notation number and conflates it with a different-looking string. This behavior has been reported with text-formatted cells and leads to incorrect duplicate detection and conditional totals.

Why this is dangerous

This is not a “your data has spaces” issue. The scary part is:

• Your dataset can look perfectly clean.
• The column can be formatted as Text.
• A spot-check of matching rows may look correct.
• Yet your SUMIF/SUMIFS/COUNTIF/COUNTIFS totals can be wrong because Excel is internally applying numeric coercion to the criterion (and/or comparisons) when the string resembles scientific notation.

In large datasets, it’s extremely easy to miss. Unless you already know this behavior exists, it’s close to impossible to debug (especially when only a handful of codes trigger it).

Does this still happen in current Excel versions?

This scientific-notation coercion behavior is not limited to old Excel versions. There are recent reports (including Excel 365 / Microsoft 365 Apps) of Excel still forcing scientific-notation behavior and performing unwanted conversions even when columns are set to Text.
So this appears to be an ongoing behavior/bug class rather than something fixed in modern builds.

Solution/Ask

Is Microsoft aware of this specific IF-family criteria coercion problem (text that looks like scientific notation causing false matches)? It would be great to have:

• A documented note/warning for SUMIF/SUMIFS/COUNTIF/COUNTIFS criteria evaluation, or
• A fix so Text stays Text during criteria matching; no scientific-notation coercion.