Contact
12 programs
Explore the site

COUNTBLANK Function (OpenOffice Calc)

Statistical Beginner OpenOffice Calc Introduction not established

The COUNTBLANK function in OpenOffice Calc counts empty cells in a range. Learn syntax, examples, common errors, and best practices.

Verification status: Consult the documentation, examples and any stated test scope below. How formula examples are checked

Availability and version history

  • Introduced: Documented by Apache OpenOffice 4.1.16; original introduction not established.
  • Availability: Supported in Apache OpenOffice 4.1.16, verified against its function registry. This identifies a supported release, not a final working version.
  • Scope: Desktop Calc, using the English function name. Availability in other spreadsheet applications is separate.
  • Checked: 2026-10-14 — function reference; versioned support. Documentation/source check; not a new native test. Existing example-specific test claims below retain their stated scope.

The COUNTBLANK function in OpenOffice Calc counts the number of empty cells in a range. It is essential for data validation, completeness checks, form auditing, and identifying missing entries in large datasets. Unlike COUNTA, which counts non‑empty cells, COUNTBLANK focuses exclusively on cells that contain nothing at all.

What the COUNTBLANK Function Does

  • Counts truly empty cells
  • Ignores cells containing text, numbers, formulas, or errors
  • Works across sheets
  • Useful for data completeness and quality checks
  • Ideal for forms, surveys, and imported datasets

COUNTBLANK is the simplest and most reliable way to detect missing data.

Syntax

COUNTBLANK(range)

Arguments:

  • range — The cells to evaluate
COUNTBLANK does not count formulas that return "" (empty string) as empty. These are treated as non‑empty.

Basic Examples

Count empty cells in a range

=COUNTBLANK(A1:A10)

Count empty cells across multiple ranges

=COUNTBLANK(A1:C10)

Count empty cells in a mixed dataset

If A1:A5 contains: 10, "Text", "", =B1, empty:

=COUNTBLANK(A1:A5)

Result: 1
Only the truly empty cell is counted.

Advanced Examples

Count empty cells across sheets

=COUNTBLANK(Sheet1.A1:A100)

Count empty cells excluding formulas returning empty strings

Use COUNTIF:

=COUNTIF(A1:A100; "=")

This counts cells that are truly empty.

Count empty cells including formulas returning empty strings

Use:

=COUNTIF(A1:A100; "")

Count empty cells in a 3D range

=COUNTBLANK(Sheet1:Sheet5.A1:A10)

Count empty cells in filtered data

COUNTBLANK does not ignore filtered rows.
To count only visible empty cells:

=SUBTOTAL(103; A1:A100)

Function code 103 = COUNTA (ignore hidden rows).
Then subtract from total visible rows.

Example:

=SUBTOTAL(3; A1:A100)  
=SUBTOTAL(2; A1:A100)

Common Errors and Fixes

COUNTBLANK returns 0 unexpectedly

Possible causes:

  • Cells contain formulas returning ""
  • Cells contain spaces (" ")
  • Cells contain invisible characters
  • Cells contain error values

Fix:
Use TRIM, CLEAN, or inspect formulas.

COUNTBLANK returns a higher number than expected

Possible causes:

  • Imported data lost formatting
  • Cells appear filled but contain no actual content
  • Hidden characters were removed

Err:504 — Parameter error

Occurs when:

  • A malformed range is used
  • A semicolon is missing

Err:508 — Missing parenthesis

Usually caused by:

  • Missing )
  • Using commas instead of semicolons

Best Practices

  • Use COUNTBLANK to detect missing data
  • Use COUNTA to count non‑empty cells
  • Use COUNTIF(A1:A100; “=”) to detect truly empty cells
  • Clean imported data to remove invisible characters
  • Use named ranges for cleaner formulas
  • Combine COUNTBLANK with conditional formatting to highlight missing entries
COUNTBLANK is ideal for validating forms and ensuring required fields are filled.
Example: =COUNTBLANK(A2:F2) shows how many fields a user left empty.