Contact
Eight programs
Explore the site

Counting and criteria · 7-minute guide

Why COUNTIF Returns Zero or the Wrong Total

Diagnose COUNTIF mistakes from hidden spaces, numeric/text mismatches, date criteria and range alignment, with a reproducible Calc example.

When COUNTIF reports zero even though matching rows appear on screen, a formatting change is usually not enough. Check what the cell actually contains and how the function interprets the search criterion.

Reproduce a visible mismatch

In LibreOffice Calc, place these literal text values in cells B2:B7:

Cell Visible status Notes
B2 Open Ordinary text
B3 Closed Ordinary text
B4 Open Ordinary text
B5 Open␠ Has one trailing space after “Open”
B6 Closed Ordinary text
B7 Pending Ordinary text

The symbol ␠ illustrates a space; do not type the symbol itself. Enter the text Open in B5, including one space after the word. Now enter:

=COUNTIF(B2:B7;"Open")

With whole-cell matching enabled, expected count: 2, not 3, because B5 contains an extra character. Depending on your LibreOffice Calc search settings, text-pattern criteria and whole-cell matching can behave differently. Those settings matter; this isn’t an application-tested universal result. The LibreOffice COUNTIF reference explains the whole-cell matching setting and criteria rules.

Troubleshoot using a short checklist

  1. Count the characters, not just the displayed text. Test =LEN(B5) and =LEN(B2). The example lengths are 5 and 4 respectively.
  2. Review the search setting in Calc’s calculation options. Wildcard, regular-expression and whole-cell criteria settings can influence text criteria.
  3. Confirm the range. B2:B7 includes six cells; B3:B7 misses the first data row. Two-criteria functions require aligned ranges.
  4. Distinguish text from numbers. A source might contain numeric 25, the text “25”, or a date serial displayed as 25. Do not assume they compare identically.
  5. Use numeric comparison criteria deliberately. The expression ">=100" is a criterion string, not the same kind of value as 100.
  6. Audit your actual blank cells. An empty cell and a formula returning "" can produce different results with counting functions and filtering operations.

Download the status practice CSV. The fourth data row intentionally contains a trailing space in the status; inspect the raw text before cleaning it.

For a safe cleanup, create a helper column in C2 with:

=TRIM(B2)

Copy it through C7 and compare the original with the cleaned output before replacing anything. In the sample, B5 becomes Open in C5. This removes ordinary excess spaces, not necessarily every Unicode space. Never run a destructive bulk replacement on unknown data without keeping a copy.

Dates need real date values

If column A contains genuine Calc dates, and you need entries in October 2026, use separate start and end boundaries rather than typing ambiguous region-specific date strings into a criterion:

=COUNTIFS(A2:A100;">="&DATE(2026;10;1);A2:A100;"<"&DATE(2026;11;1))

The upper bound excludes November 1 and includes all October dates, even when dates have a time component. This requires actual date/time values in the range—not text that merely looks like dates. Follow Why CSV dates go wrong before trusting imported data.

When to use COUNTIFS instead

Use COUNTIF for one range and criterion. Use COUNTIFS when every row must meet multiple tests, for example Status = Open and Amount ≥ 100. Both criteria ranges must cover corresponding rows. The Calc COUNTIFS guide provides detailed syntax and examples.

Verification note: LibreOffice official COUNTIF/COUNTIFS documentation was checked. The sample counts and date-boundary logic were reasoned independently; the sample was not run in LibreOffice Calc for this page. No genuine application screenshots are included.