Contact
12 programs
Explore the site

COUNTA Function (OpenOffice Calc)

Statistical Beginner OpenOffice Calc OpenOffice.org 3.0

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.

Verification status: Consult the documentation and examples below. Native application testing has not been documented for this guide. How formula examples are checked

The COUNTA function in OpenOffice Calc counts all non‑empty cells, regardless of whether they contain text, numbers, formulas, logical values, or error values. It is one of the most important functions for evaluating data completeness, validating imported datasets, and analyzing mixed‑type information.

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: =COUNTA(A1:A100) = 100 ensures all rows contain data.