Browse

LibreOffice Calc · Formula troubleshooting · Intermediate

LibreOffice COUNTIF Returns Zero: Troubleshooting Guide

Fix COUNTIF returning zero despite matching cells in LibreOffice Calc. Check ranges, types, criteria, spaces and wildcard settings.

Updated 10 October 2026 · Formula checks: testing standards

COUNTIF should count cells that match a criterion, but a result of 0 does not always mean there are no visible matches. Begin with a known-good example, then check the range, cell types and exact text in your own workbook.

A five-row test that should return 3

Enter this data in cells A2:A6:

Cell Value
A2 North
A3 South
A4 North
A5 East
A6 North

In another cell, enter:

=COUNTIF(A2:A6;"North")

Expected result: 3. If this works and your original formula does not, compare the data carefully.

Seven checks for a zero result

  1. Wrong range: confirm that the actual data is in the referenced cells and not a neighbouring column or sheet.
  2. Text criteria syntax: text such as North belongs in quotation marks; for instance, "North".
  3. Operators and references: for values greater than the number in B1 use ">"&B1, not ">B1".
  4. Numbers stored as text: test individual cells with =ISTEXT(A2) and =ISNUMBER(A2) when comparing quantities. See numbers stored as text.
  5. Hidden characters: inspect =LEN(A2). A space, tab or non-breaking space can make visible values differ.
  6. Partial matching assumptions: the behavior of wildcards and regular expressions depends on Calc’s calculation settings. A literal text search may behave differently from "North.*".
  7. Matching whole cells: check Tools → Options → LibreOffice Calc → Calculate for options concerning whole-cell search criteria and wildcards/regular expressions.

Compare text without guessing

If the cell looks like North but =LEN(A2) returns 6, check for an extra character. =TRIM(A2) can help with ordinary spaces, though it may not remove all invisible characters. Inspect the original input before applying global cleaning.

More reliable numeric criteria

Suppose A2:A6 contains the actual numbers 1, 2, 3, 4, 5 and B1 contains 3. This formula counts values greater than B1:

=COUNTIF(A2:A6;">"&B1)

Expected result: 2 (the values 4 and 5).

Common questions

Is COUNTIF case-sensitive? It ordinarily ignores case in common configurations; if case is essential, use a specifically tested case-sensitive method.

Can COUNTIF handle multiple independent conditions? Use COUNTIFS and make sure all range dimensions match. The COUNTIF reference covers one condition.

Official reference: LibreOffice Help. This tutorial focuses on LibreOffice Calc; menu names and behavior can vary by version, operating system and locale.