COUNTBLANK Function (LibreOffice Calc)
The COUNTBLANK function in LibreOffice Calc counts empty cells in a range. This guide explains syntax, examples, edge cases, 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 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)
="" 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.
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
COUNTandCOUNTAto understand dataset composition - Clean imported data before analysis
- Use TRIM and CLEAN to remove invisible characters
- Use named ranges for cleaner formulas