COUNTA Function (OpenOffice Calc)
The COUNTA function in OpenOffice Calc counts non‑empty cells, including text, numbers, formulas, and logical values. Learn syntax, examples, common errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the COUNTA Function Does ▾
- Counts any non‑empty cell
- Includes text, numbers, formulas, logical values, and errors
- Ignores only truly empty cells
- Works across sheets
- Useful for data validation and completeness checks
- Ideal for mixed‑type datasets
COUNTA is essential when you need to know how many cells contain something, regardless of type.
Syntax ▾
COUNTA(value1; value2; ...)
Arguments:
- value1, value2, … — Individual values, cell references, or ranges
COUNTA counts everything except empty cells, including formulas that return empty strings (
"").
Basic Examples ▾
Count non‑empty cells in a range
=COUNTA(A1:A10)
Count non‑empty cells across multiple ranges
=COUNTA(A1:A10; C1:C10)
Count text, numbers, and formulas
If A1:A5 contains: 10, "Text", "", =B1, FALSE:
=COUNTA(A1:A5)
Result: 4
(The empty string "" counts as non‑empty.)
Count non‑empty values in a list
=COUNTA(5; ""; "Hello"; 0)
Result: 3
Advanced Examples ▾
Count non‑empty cells across sheets
=COUNTA(Sheet1.A1:A100)
Count non‑empty visible cells only
Use SUBTOTAL instead:
=SUBTOTAL(3; A1:A100)
Function code 3 = COUNTA.
Count non‑empty cells with conditions (workaround)
OpenOffice Calc does not have COUNTAIF, but you can use:
=COUNTIF(A1:A100; "<>")
This counts all non‑empty cells except formulas returning "".
Count non‑empty cells excluding formulas
=COUNTIF(A1:A100; "<>""")
Count non‑empty cells in a 3D range
=COUNTA(Sheet1:Sheet5.A1:A10)
Counts all non‑empty cells across multiple sheets.
Common Errors and Fixes ▾
COUNTA returns a higher number than expected
Possible causes:
- Cells contain formulas returning
"" - Cells contain spaces (
" ") - Cells contain invisible characters
- Cells contain error values
Fix:
Use TRIM or CLEAN, or check for formulas returning empty strings.
COUNTA returns a lower number than expected
Possible causes:
- Cells appear filled but are actually empty
- Imported data lost formatting
- Hidden characters were removed
Err:504 — Parameter error
Occurs when:
- A malformed range is used
- A semicolon is missing
- A text argument is not quoted properly
Err:508 — Missing parenthesis
Usually caused by:
- Missing
) - Using commas instead of semicolons
Best Practices ▾
- Use COUNTA for mixed‑type datasets
- Use COUNT for numeric‑only datasets
- Use COUNTBLANK to count empty cells
- Use COUNTIF(A1:A100; “<>”) to exclude formulas returning
"" - Clean imported data to avoid invisible characters
- Use named ranges for cleaner formulas
COUNTA is ideal for checking whether a dataset is complete.
Example:
Example:
=COUNTA(A1:A100) = 100 ensures all rows contain data.