Browse

COUNTBLANK Function (LibreOffice Calc)

Math Beginner LibreOffice Calc LibreOffice 3.0

The COUNTBLANK function in LibreOffice Calc counts empty cells in a range. This guide explains syntax, examples, edge cases, errors, and best practices.

The **COUNTBLANK** function counts how many cells in a range are completely empty. It is essential for data cleaning, validation, and preparing datasets for analysis in LibreOffice Calc.

This guide provides syntax, examples, advanced techniques, common errors, and best practices, making it one of the most complete COUNTBLANK references available for LibreOffice Calc.

What the COUNTBLANK Function Does

  • Counts truly empty cells
  • Ignores cells containing text, numbers, formulas, or errors
  • Works with ranges only (not individual values)
  • Helps identify missing data
  • Useful for data validation and cleanup
  • Works across large datasets efficiently

COUNTBLANK is ideal for detecting gaps in data entry.

Syntax

COUNTBLANK(range)
COUNTBLANK accepts only one argument, and it must be a range.
Cells containing formulas like ="" are not considered blank.

Basic Examples

Count empty cells in a range

=COUNTBLANK(A1:A10)

Counts how many cells in A1 through A10 are empty.

Count blanks in multiple ranges (indirectly)

COUNTBLANK cannot take multiple ranges, but you can add results:

=COUNTBLANK(A1:A10) + COUNTBLANK(C1:C10)

Count blanks in a row

=COUNTBLANK(1:1)

Counts empty cells across the entire first row.

Count blanks in a column

=COUNTBLANK(A:A)

Counts empty cells in column A.

COUNTBLANK is extremely useful for data completeness checks.

Advanced Examples

Count blanks across sheets

COUNTBLANK does not support multi‑sheet ranges directly, but you can combine results:

=COUNTBLANK(Sheet1.A1:A10) + COUNTBLANK(Sheet2.A1:A10)

Count blanks excluding cells with formulas

To count cells that are visually blank but contain formulas:

=SUMPRODUCT(ISFORMULA(A1:A10)=FALSE; A1:A10="")

Count blanks with conditions (indirect)

To count blanks only when another condition is met:

=SUMPRODUCT((A1:A10="") * (B1:B10>100))

Count blanks in filtered data

COUNTBLANK counts all blanks, visible or hidden.
To count only visible blanks:

=SUMPRODUCT(SUBTOTAL(103; OFFSET(A1; ROW(A1:A10)-ROW(A1); 0)); A1:A10="")

(Advanced technique for filtered datasets.)

Common Errors and Fixes

COUNTBLANK returns 0 unexpectedly

Possible causes:

  • Cells contain formulas returning empty strings (="")
  • Cells contain spaces
  • Cells contain invisible characters
  • Cells contain formatting but no visible content

Fix:
Check with:
=LEN(A1)

COUNTBLANK counts fewer blanks than expected

Possible causes:

  • Imported CSV data contains hidden characters
  • Cells contain non‑breaking spaces

Fix:
Use CLEAN or TRIM:
=LEN(TRIM(A1))

Err:504 — Parameter error

Occurs when:

  • The argument is not a range
  • A semicolon is used incorrectly

Best Practices

  • Use COUNTBLANK to detect missing data
  • Use COUNT and COUNTA to understand dataset composition
  • Clean imported data before analysis
  • Use TRIM and CLEAN to remove invisible characters
  • Use named ranges for cleaner formulas
COUNTBLANK is essential for data validation, especially in forms, surveys, and imported datasets.

Reproducible example (tested in LibreOffice Calc)

This standalone example was calculated in LibreOffice Calc 25.2.3.2 and matched the expected value. It is a practical cross-check of one usage, not proof that every example elsewhere on this page is correct.

Leave A9:A11 empty in the practice sheet when trying this example.

=COUNTBLANK(A9:A11)

Expected result: 3.

Count unused cells in the specified sample range.

If the result differs, check the referenced cells, argument separators and number formatting. For more information, consult the LibreOffice Calc official help.