https://bugs.documentfoundation.org/show_bug.cgi?id=171581
Bug ID: 171581
Summary: COUNTIF inconsistent when matching numeric text vs
numeric values (differs from Excel/Google Sheets
behavior)
Product: LibreOffice
Version: 26.2.1.2 release
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Created attachment 206487
--> https://bugs.documentfoundation.org/attachment.cgi?id=206487&action=edit
Example COUNTIF
Steps to Reproduce
Open LibreOffice Calc
Enter the following values:
Cell B3: 1 (number)
Cell B4: 2 (number)
Cell C3: '123 (text, leading apostrophe)
Cell C4: 123 (number)
Enter the formulas:
In E3:
=COUNTIF($B$3:$C$4;C3)
In E4:
=COUNTIF($B$3:$C$4;C4)
Actual Result (LibreOffice Calc)
COUNTIF(...;C3) → 2
COUNTIF(...;C4) → 1
Expected Result
Both formulas should return: 2
Because:
'123 (text) and 123 (number) are treated as equivalent in:
Microsoft Excel
Google Sheets
echnical Issue
COUNTIF in Calc performs strict type comparison:
text "123" ≠ numeric 123
This leads to inconsistent behavior in mixed-type datasets, especially when:
importing data (CSV, databases, etc.)
working with user-entered values that mix text and numbers
other functions perform implicit coercion but COUNTIF does not
Expected Behavior (Proposal)
Possible approaches:
Option A (Compatibility)
Match Excel / Google Sheets behavior
Perform implicit numeric coercion when possible
Option B (Explicit behavior separation)
Keep current strict behavior
Introduce a new function, e.g.:
COUNTIF.STRICT
or COUNTIF.OOO
Option C (Configurable)
Add a setting:
“Enable flexible type matching in statistical functions”
Impact
Breaks interoperability with Excel / Google Sheets
Produces unexpected results in real-world datasets
Errors are silent and hard to detect
Additional Note
The current behavior is logically consistent with strict typing, but
inconsistent with user expectations and with the behavior of other spreadsheet
software.
System:
Version: 26.2.1.2 (X86_64)
Build ID: 8399f6259d8c87f40e7255cdb3c9b958f5e08948
CPU threads: 12; OS: Linux 6.11; UI render: default; VCL: gtk3
Locale: es-ES (es_ES.UTF-8); UI: en-US
Calc: threaded
--
You are receiving this mail because:
You are the assignee for the bug.
