COUNTIFS Function (OpenOffice Calc)

• 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.

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

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

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.

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")

• 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