COUNTIFS Function (LibreOffice Calc)
The COUNTIFS function in LibreOffice Calc counts cells that meet multiple conditions across one or more ranges. This guide explains syntax, operators, wildcards, examples, errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✖ |
| Wps | ✔ |
| Zoho | ✔ |
What the COUNTIFS Function Does â–¾
- Counts cells that satisfy two or more conditions
- Supports numeric, text, date, logical, and wildcard criteria
- Allows conditions across multiple ranges
- Requires all ranges to be the same size
- Works efficiently on large datasets
- Ideal for filtering, reporting, and analytics
COUNTIFS is the multi‑condition extension of COUNTIF.
Syntax â–¾
COUNTIFS(range1; criteria1; range2; criteria2; ...)
Each range must be the same size, or COUNTIFS will return an error.
Criteria containing operators or text must be in quotes.
Basic Examples â–¾
Count values greater than 50 AND less than 100
=COUNTIFS(A1:A10; ">50"; A1:A10; "<100")
Count rows where Region = “North” AND Sales > 1000
=COUNTIFS(A1:A100; "North"; B1:B100; ">1000")
Count rows where Status = “Open” AND Priority = “High”
=COUNTIFS(A1:A100; "Open"; B1:B100; "High")
Count rows where Date is in 2025 AND Amount > 500
=COUNTIFS(A1:A100; ">=2025-01-01"; A1:A100; "<=2025-12-31"; B1:B100; ">500")
Text & Wildcard Examples â–¾
Count text starting with “A” AND ending with “Z”
=COUNTIFS(A1:A100; "A*"; A1:A100; "*Z")
Count cells containing “car” AND length > 5
=COUNTIFS(A1:A100; "*car*"; B1:B100; ">5")
Count case‑sensitive matches
COUNTIFS is case‑insensitive.
For case‑sensitive logic, use SUMPRODUCT:
=SUMPRODUCT(EXACT(A1:A100; "Apple") * (B1:B100>100))
Advanced Examples â–¾
Use cell references as criteria
=COUNTIFS(A1:A100; "=" & D1; B1:B100; ">=" & E1)
Count rows where value is between two cells
=COUNTIFS(A1:A100; ">=" & D1; A1:A100; "<=" & E1)
Count rows where text is NOT equal to a value
=COUNTIFS(A1:A100; "<>North")
Count rows where two conditions apply to different columns
=COUNTIFS(A1:A100; "North"; C1:C100; "<>")
Count dates before a specific date AND category = “A”
=COUNTIFS(A1:A100; "<" & DATE(2025;1;1); B1:B100; "A")
Count errors with multiple conditions
=COUNTIFS(A1:A100; "#N/A"; B1:B100; ">0")
Common Errors and Fixes â–¾
Err:504 — Parameter error
Occurs when:
- Ranges are different sizes
- Criteria is malformed
- Semicolons are incorrect
COUNTIFS 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)
COUNTIFS is slow on large datasets
Use:
- Named ranges
- Helper columns
- Database functions (DSUM, DCOUNT) for structured data
COUNTIFS does not support OR logic
Use SUM of multiple COUNTIFS:
=COUNTIFS(A1:A100; "North") + COUNTIFS(A1:A100; "South")
Or use SUMPRODUCT for complex OR logic.
Best Practices â–¾
- Ensure all ranges are the same size
- Always quote criteria containing operators
- Use cell references for dynamic criteria
- Use COUNTIF for single‑condition logic
- Use SUMPRODUCT for advanced OR/AND combinations
- Clean imported data before applying criteria
COUNTIFS is essential for dashboards, KPI tracking, and multi‑condition reporting.