COUNTBLANK Function (OpenOffice Calc)
The COUNTBLANK function in OpenOffice Calc counts empty cells in a range. Learn syntax, examples, common errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
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:
Example:
=COUNTBLANK(A2:F2) shows how many fields a user left empty.