COUNT can return a different total in LibreOffice than in Excel
When a range you pass to COUNT contains a logical (TRUE/FALSE) cell, LibreOffice Calc
counts that cell as a number and Excel does not, so the identical =COUNT(range)
returns a number one larger in LibreOffice. Nothing errors; the total is just quietly off.
The surprise
A boolean sitting in a referenced range is excluded from COUNT in Excel but included in LibreOffice. Every numeric cell still counts in both; only the treatment of the boolean cell differs, which is enough to shift a total by one.
A minimal example
Fill a column like this: A1=10, A2="hello" (text),
A3 blank, A4=TRUE, A5=20, A6 blank.
| Formula | Excel (documented) | Google Sheets | LibreOffice Calc 25.8.7.3 (executed) |
|---|---|---|---|
| =COUNT(A1:A6) | 2 | Not yet executed | 3 |
| =COUNT(A1:A1) with A1=TRUE | 0 | Not yet executed | 1 |
In the first row Excel counts only A1 and A5 (the two real numbers);
LibreOffice also counts A4, the boolean. In the second row the range is a single
boolean cell: Excel counts zero numbers, LibreOffice counts one. The Excel figures are the
documented-expected values recorded in our test corpus; the LibreOffice figures are what our
harness actually computed by recalculating the workbook in LibreOffice Calc 25.8.7.3. We have not yet executed this
case in Google Sheets, so that column is left honest.
Why it happens
Microsoft's COUNT documentation draws a deliberate line between arguments you type
and values you reference: "Logical values and text representations of numbers that you
type directly into the list of arguments are counted," while logical values, text, and blanks
inside a referenced range are ignored. So =COUNT(1,TRUE) is 2 in Excel (the typed
TRUE counts), but a TRUE living in a cell that a range points at does not. LibreOffice does not
make that distinction for a boolean cell: it treats the cell's underlying value as the number 1
and counts it. See Microsoft's
COUNT function reference.
How to migrate safely
The gap is in how each engine types a boolean cell, so the durable fix is structural: keep TRUE/FALSE flags out of any numeric range that COUNT scans. Put flags in their own column, and when you genuinely want to count them use a criterion both engines agree on:
=COUNTIF(B1:B6,TRUE) to count the flags, and =COUNT(A1:A6) over a
column that holds only numbers.
If mixing is unavoidable, re-verify every COUNT total after moving the workbook, because the difference is silent. A single stray boolean in a scanned range is enough to make a headline number disagree between the two apps.