Contact
12 programs
Explore the site

COUNTIFS Function (LibreOffice Calc)

Math Intermediate LibreOffice Calc LibreOffice 4.0

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.

Verification status: Selected examples were executed in LibreOffice Calc 25.2.3.2. This does not establish that every formula and edge case was tested. How formula examples are checked

The **COUNTIFS** function counts how many rows or cells meet **multiple conditions** across one or more ranges. It is one of the most powerful conditional functions in LibreOffice Calc, ideal for filtering datasets, building dashboards, and performing multi‑criteria analysis.

Native-tested visual example: Count sales meeting two conditions

Six sales orders list a region and a value. COUNTIFS counts North orders above 500.

Enter this formula in cell E5:

=COUNTIFS(B5:B10;"North";C5:C10;">500")

Expected result: 2. Rows 5 and 8 satisfy both criteria; the third North sale is only 300. Both criteria ranges must cover the same rows.

LibreOffice Calc 25.2 actual screenshot of the COUNTIFS function with source cells, the formula in the formula bar, and result 2 North sales above 500

Real screenshot captured from LibreOffice Calc 25.2.3.2. The selected E5 cell shows the calculated result and the formula bar displays the entered formula.

Download the editable COUNTIFS example (.ods) — change the sample input cells and watch the calculation update.

Why it works: Rows 5 and 8 satisfy both criteria; the third North sale is only 300. Both criteria ranges must cover the same rows. Watch for: Comparison operators such as >500 belong inside quotation marks when used as criteria.

Verification: Example calculated in LibreOffice Calc 25.2.3.2. Screenshot is a genuine application capture, not a mockup.

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; ">=" & DATE(2025;1;1); A1:A100; "<" & DATE(2026;1;1); B1:B100; ">500")

Text & Wildcard Examples

Count text starting with “A” AND ending with “Z”

=COUNTIFS(A1:A100; "A*"; A1:A100; "*Z")

Count rows where the name contains “car” AND quantity is greater than 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")

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.

Reproducible example (tested in LibreOffice Calc)

This standalone example was calculated in LibreOffice Calc 25.2.3.2 and matched the expected value. It is a practical cross-check of one usage, not proof that every example elsewhere on this page is correct.

Use this small practice sheet for the ranges referenced in the formula:

Row A (Quantity) B (Amount) C (Category)
1 1 10 apple
2 2 20 pear
3 3 30 apple
4 4 40 pear
5 5 50 apple 
=COUNTIFS(C1:C5;"apple";B1:B5;">20")

Expected result: 2.

Count rows that match both a text label and a numeric condition.

If the result differs, check the referenced cells, argument separators and number formatting. For more information, consult the LibreOffice Calc official help.

Practical troubleshooting tutorials