Contact
12 programs
Explore the site

COUNTIF Function (OpenOffice Calc)

Statistical Beginner OpenOffice Calc OpenOffice.org 3.0

The COUNTIF function in OpenOffice Calc counts cells that meet a single condition. Learn syntax, examples, wildcard rules, 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 COUNTIF function in OpenOffice Calc counts the number of cells that meet a single condition. It supports numeric comparisons, text matching, wildcard patterns, and logical expressions. Whether you’re counting sales above a threshold, matching categories, or filtering text patterns, COUNTIF is one of the most essential conditional functions in OpenOffice Calc.

What the COUNTIF Function Does

  • Counts cells that meet a single condition
  • Supports numeric, text, and logical criteria
  • Allows comparison operators (>, <, >=, <>, etc.)
  • Supports wildcard matching (* and ?)
  • Works across sheets
  • Ideal for filtering, categorizing, and validating data

COUNTIF is the foundation of conditional counting in spreadsheets.

Syntax

COUNTIF(range; criteria)

Arguments:

  • range — The cells to evaluate
  • criteria — The condition to apply
Criteria must be enclosed in quotes unless referencing a cell.

Wildcard Rules

COUNTIF supports:

  • * — matches any sequence of characters
  • ? — matches any single character
  • ~ — escapes literal * or ?

Examples:

  • "A*" matches any text starting with A
  • "*Service*" matches text containing “Service”
  • "???" matches any 3‑character string
  • "~*" matches a literal asterisk

Basic Examples

Count cells equal to a value

=COUNTIF(A1:A10; 5)

Count cells greater than a number

=COUNTIF(A1:A10; ">50")

Count cells equal to text

=COUNTIF(A1:A10; "North")

Count cells matching a wildcard pattern

=COUNTIF(A1:A10; "Jan*")

Matches any text beginning with “Jan”.

Count non‑empty cells (COUNTA alternative)

=COUNTIF(A1:A100; "<>")

Counts all cells that are not empty.

Advanced Examples

Count cells across sheets

=COUNTIF(Sheet1.A1:A100; ">0")

Count cells using a cell‑based criterion

=COUNTIF(A1:A10; C1)

If C1 contains ">=500", the formula uses that condition.

Count cells not equal to something

=COUNTIF(A1:A10; "<>North")

Count cells containing partial text

=COUNTIF(A1:A100; "*Service*")

Count dates before or after a specific date

=COUNTIF(A1:A100; ">=" & DATE(2025;1;1))

Count text values only

=COUNTIF(A1:A100; "<>") - COUNT(A1:A100)

Count blank cells (alternative to COUNTBLANK)

=COUNTIF(A1:A100; "=")

Count cells with formulas returning empty strings

=COUNTIF(A1:A100; "")

Common Errors and Fixes

COUNTIF returns 0 unexpectedly

Possible causes:

  • Criteria missing quotes
  • Text numbers not matching numeric criteria
  • Hidden spaces in cells
  • Wildcards not used correctly
  • Dates not constructed properly

COUNTIF includes values you expected it to ignore

COUNTIF includes:

  • Text numbers ("123") when criteria is text
  • Dates (stored as numbers)
  • Formulas returning text

COUNTIF excludes values you expected it to include

COUNTIF ignores:

  • Logical values (TRUE/FALSE)
  • Errors
  • Empty cells

Err:504 — Parameter error

Occurs when:

  • A malformed range is used
  • A semicolon is missing
  • A text criterion is missing quotes

Err:508 — Missing parenthesis

Usually caused by:

  • Missing )
  • Using commas instead of semicolons

Best Practices

  • Use quotes for all text and comparison criteria
  • Use cell references for dynamic criteria
  • Use wildcards for flexible text matching
  • Clean imported data to remove hidden spaces
  • Use COUNTIFS for multi‑criteria counting
  • Use named ranges for cleaner formulas
For dynamic dashboards, store criteria in separate cells and reference them:
Example: =COUNTIF(Region; F1)