Contact
12 programs
Explore the site

COUNTIFS Function (OpenOffice Calc)

Statistical Intermediate OpenOffice Calc OpenOffice.org 4.0

The COUNTIFS function in OpenOffice Calc counts cells that meet multiple conditions. 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 COUNTIFS function in OpenOffice Calc counts the number of cells that meet multiple conditions simultaneously. It supports numeric comparisons, text matching, wildcard patterns, and logical expressions across multiple aligned ranges. When you need precise filtering based on several criteria, COUNTIFS is the most powerful conditional counting tool in OpenOffice Calc.

What the COUNTIFS Function Does

  • Counts cells that meet two or more conditions
  • Supports numeric, text, date, and logical criteria
  • Allows comparison operators (>, <, >=, <>, etc.)
  • Supports wildcard matching (* and ?)
  • Evaluates each condition across aligned ranges
  • Works across sheets
  • Ideal for dashboards, analytics, and filtered datasets

COUNTIFS is essential for multi‑criteria data analysis.

Syntax

COUNTIFS(criteria_range1; criteria1; criteria_range2; criteria2; ...)

Arguments:

  • criteria_range1 — The first range to evaluate
  • criteria1 — The first condition
  • criteria_range2; criteria2; … — Additional range/condition pairs
All criteria ranges must be the same size.

Wildcard Rules

COUNTIFS 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 meeting two conditions

=COUNTIFS(A1:A100; "North"; B1:B100; ">1000")

Count cells matching text and numeric criteria

=COUNTIFS(A1:A50; "Service"; B1:B50; "<=500")

Count cells using wildcard matching

=COUNTIFS(A1:A200; "Jan*"; B1:B200; ">0")

Count cells using cell‑based criteria

=COUNTIFS(A1:A100; E1; B1:B100; E2)

If:

  • E1 contains "North"
  • E2 contains ">500"

…the formula uses those conditions dynamically.

Advanced Examples

Count cells across sheets

=COUNTIFS(Sheet1.A1:A500; "West"; Sheet1.B1:B500; "<100")

Count cells within a date range

=COUNTIFS(A1:A1000; ">=" & DATE(2025;1;1); A1:A1000; "<=" & DATE(2025;12;31))

Count cells not equal to something

=COUNTIFS(A1:A100; "<>North"; B1:B100; ">0")

Count cells with partial text match

=COUNTIFS(A1:A100; "*Service*"; B1:B100; ">100")

Count cells using numeric thresholds

=COUNTIFS(B1:B200; ">50"; C1:C200; "<100")

OR logic workaround

OpenOffice Calc does not support OR conditions directly in COUNTIFS.
Use two COUNTIFS and add them:

=COUNTIFS(A1:A100; "North") +
  COUNTIFS(A1:A100; "South")

Count blank cells with multiple conditions

=COUNTIFS(A1:A100; "="; B1:B100; ">0")

Common Errors and Fixes

COUNTIFS returns 0 unexpectedly

Possible causes:

  • Criteria missing quotes
  • Text numbers not matching numeric criteria
  • Hidden spaces in cells
  • Wildcards not used correctly
  • Date criteria not constructed properly
  • Criteria ranges not the same size

COUNTIFS includes values you expected it to ignore

COUNTIFS includes:

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

COUNTIFS excludes values you expected it to include

COUNTIFS ignores:

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

Err:504 — Parameter error

Occurs when:

  • Criteria ranges differ in size
  • A malformed range is used
  • A text criterion is missing quotes

Err:508 — Missing parenthesis

Usually caused by:

  • Missing )
  • Using commas instead of semicolons

Best Practices

  • Ensure all criteria ranges are the same size
  • Use cell references for dynamic criteria
  • Use wildcards for flexible text matching
  • Clean imported data to remove hidden spaces
  • Use helper columns for complex logic
  • Use named ranges for cleaner formulas
For multi‑criteria dashboards, store each condition in its own cell and reference them:
Example: =COUNTIFS(Region; F1; Category; F2; Quarter; F3)