Calc COUNTIF changes when regex alternatives are reordered: test the whole-cell boundary
By ON Lab · Sources checked November 8, 2026
If Calc counts a cell for abcde|abc but misses it for abc|abcde, check both regular-expression evaluation and the whole-cell criterion setting. For an exact choice between those two labels, test the grouped, anchored criterion ^(abc|abcde)$ on a copy. Turning off whole-cell matching changes the meaning of other criteria too.
A reader reported this ordering difference in LibreOffice 26.8.1.1. The discussion already identifies an end-anchor workaround. We tested a wider, original seven-value fixture in native LibreOffice Calc 26.8.2.2 on macOS arm64, comparing three calculation modes. The useful distinction is whether you want exact labels or text containing either label, and what happens to other formulas when a setting changes.
Use a small range with negative controls
Put these seven text values in A2:A8. The third value has one ordinary trailing space, represented below by the notation [space]. Do not type that notation as part of the value.
abc
abcde
abcde[space]
xabcde
abcdeX
ABC
a.cThe exact-label result for this fixture is three cells: abc, abcde and ABC. Our COUNTIF results included the uppercase value. The prefixed, suffixed and trailing-space values are useful controls: a formula that admits them is answering a broader question.
Check the third value with =LEN(A4). Our result was 6. A displayed label can look correct while retaining an extra character. Do not trim an entire real data set until you know whether its spaces carry meaning.
Compare the actual calculation modes
The following counts came from the same fixture and formulas, exported by native Calc. “Whole regex” means regular expressions enabled and whole-cell criteria enabled. “Partial regex” keeps regular expressions enabled but disables whole-cell criteria. “Whole literal” disables both regex and wildcard evaluation while retaining whole-cell criteria.
| Criterion or calculation | Whole regex | Partial regex | Whole literal |
|---|---|---|---|
abc|abcde | 2 | 6 | 0 |
abcde|abc | 3 | 6 | 0 |
^(abc|abcde)$ | 3 | 3 | 0 |
(abc|abcde)$ | 3 | 4 | 0 |
abc | 2 | 6 | 2 |
Two COUNTIFs added: abc and abcde | 3 | 10 | 3 |
These are observed results for this version and fixture, not a claim about every Calc release or an Excel workbook. We used original flat-XML spreadsheets and headless CSV export. We did not test the reader's workbook, interact with the calculation-options dialog, or diagnose the internal matching implementation.
For exact alternatives, group and anchor the expression
=COUNTIF(A2:A8;"^(abc|abcde)$")The parentheses put both choices inside one group. The start and end anchors constrain the group to the cell's full text. In our fixture this counted three cells with either whole-cell setting, provided regex evaluation was enabled.
An end anchor alone is a different contract when partial matching is allowed:
=COUNTIF(A2:A8;"(abc|abcde)$")That returned four in partial-regex mode because the prefixed value xabcde can end with a matching alternative. Adding the start anchor excluded it. Keep grouping around the alternatives rather than placing an anchor on only one branch.
The examples use semicolons between function arguments and ordinary straight quotes around criteria. Adapt argument separators to your configuration; curly quotation marks copied from a discussion are not a safe formula delimiter.
Do not change the setting merely to make one result increase
In Calc's calculation options, inspect the regex or wildcard mode and the option governing whole-cell criteria. On Windows and Linux the route is through Tools → Options → LibreOffice Calc → Calculate; on macOS begin with LibreOffice → Preferences. Confirm the actual labels in your version.
Our simpler formula illustrates the side effect:
=COUNTIF(A2:A8;"abc")It counted two with whole-cell matching and six with partial matching. A report that previously counted an exact status could begin counting longer descriptions containing the status. Review dependent formulas before saving a calculation-setting change. Use a working copy and record the original settings so you can restore them.
Two counts added together can count the same cell twice
=COUNTIF(A2:A8;"abc")+COUNTIF(A2:A8;"abcde")This produced three under whole-cell matching, but ten under partial-regex matching, although the range contained only seven cells. A cell containing abcde also contains abc, so adding the results is not a count of distinct matching cells.
If the desired result is the union of two text alternatives, a single suitable criterion avoids that particular double-counting mistake. Decide first whether the criterion should match a whole label or any substring; the anchored and unanchored forms deliberately answer different questions.
Keep literal data separate from regex syntax
In literal mode, a vertical bar in a criterion is ordinary text rather than an OR operator. Our two unanchored alternative strings therefore returned zero. An anchored expression also returned zero in that mode; adding anchors does not enable the regex engine.
Conversely, when regex is enabled, characters such as a period can have pattern meaning. Calc's COUNTIF help documents escaping metacharacters or quoting literal text with its supported regex quoting form. Do not build a pattern by joining arbitrary labels with a vertical bar unless you have handled their special characters. This fixture uses a deliberately small fixed pair, not a general-purpose formula generator.
Verify the report before returning to the full workbook
- Record your Calc version and the two relevant calculation settings.
- Copy a few real values into a separate test range, including a near match that should be excluded.
- Check suspicious lengths and spaces without altering the source.
- Compare the current formula with a grouped, anchored expression when the goal is exact labels.
- Check a separate simple criterion to detect setting-wide side effects.
- Compare the resulting count with the actual matching rows before saving the working copy.
If the small fixture behaves differently in your version, retain it with the precise settings and formula text. A zero, an unexpectedly larger count and a formula syntax error are different observations. None alone establishes that the original workbook is corrupt.