COUNTIF Function (LibreOffice Calc)
The COUNTIF function in LibreOffice Calc counts cells that meet a single condition. This guide explains syntax, operators, wildcards, examples, errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the COUNTIF Function Does ▾
- Counts cells that match a single condition
- Supports numeric, text, logical, and wildcard criteria
- Works with comparison operators (
>,<,>=, etc.) - Supports partial text matching
- Works efficiently on large datasets
- Ideal for filtering and data validation
COUNTIF is the foundation of conditional counting in spreadsheets.
Syntax ▾
COUNTIF(range; criteria)
The criteria must be in quotes when using operators or text.
LibreOffice Calc uses semicolons (
;) to separate arguments.
Criteria Types ▾
COUNTIF supports:
- Numeric comparisons
- Text comparisons
- Wildcards (
*and?) - Logical expressions
- Cell‑based criteria
Examples:
">50"
"<=100"
"apple"
"A*"
"<>" (not equal to empty)
"=" & B1 (criteria from a cell)
Basic Examples ▾
Count values greater than 50
=COUNTIF(A1:A10; ">50")
Count cells equal to a specific number
=COUNTIF(A1:A10; 100)
Count cells equal to text
=COUNTIF(B1:B10; "North")
Count cells not equal to text
=COUNTIF(B1:B10; "<>North")
Count blank cells (alternative to COUNTBLANK)
=COUNTIF(A1:A10; "=")
Count non‑blank cells (alternative to COUNTA)
=COUNTIF(A1:A10; "<>")
Text & Wildcard Examples ▾
Count cells starting with “A”
=COUNTIF(A1:A10; "A*")
Count cells ending with “ing”
=COUNTIF(A1:A10; "*ing")
Count cells containing “car”
=COUNTIF(A1:A10; "*car*")
Count cells with exactly 5 characters
=COUNTIF(A1:A10; "?????")
Wildcards work only with text conditions.
Advanced Examples ▾
Use a cell reference as the criteria
=COUNTIF(A1:A10; "=" & B1)
Count values between 50 and 100
=COUNTIF(A1:A10; ">=50") - COUNTIF(A1:A10; ">100")
Count text but ignore case
COUNTIF is case‑insensitive by default.
Count case‑sensitive matches
Use SUMPRODUCT:
=SUMPRODUCT(EXACT(A1:A10; "Apple"))
Count dates before a specific date
=COUNTIF(A1:A10; "<" & DATE(2025; 1; 1))
Count errors
=COUNTIF(A1:A10; "#N/A")
Count formulas returning empty strings
=COUNTIF(A1:A10; "=")
Common Errors and Fixes ▾
COUNTIF returns 0 unexpectedly
Possible causes:
- Criteria missing quotes
- Numbers stored as text
- Hidden spaces or non‑breaking spaces
- Wildcards used incorrectly
Fix:
Check with:
=LEN(A1)
COUNTIF does not accept multiple ranges
This is expected.
Use COUNTIFS or SUMPRODUCT for multi‑range logic.
Err:504 — Parameter error
Occurs when:
- Semicolons are incorrect
- Criteria is malformed
- Range is invalid
COUNTIF is slow on large datasets
Use:
- Named ranges
- Helper columns
- COUNTIFS (more optimized)
Best Practices ▾
- Always quote criteria containing operators
- Use wildcards for flexible text matching
- Use COUNTIFS for multi‑condition logic
- Clean imported data before applying criteria
- Use cell references for dynamic criteria
- Avoid overly complex criteria strings
COUNTIF becomes extremely powerful when combined with SUMIF, AVERAGEIF, and COUNTIFS for full conditional analysis.