SUMIFS Function (LibreOffice Calc)
The SUMIFS function in LibreOffice Calc adds values that meet multiple conditions across one or more ranges. This guide explains syntax, operators, wildcards, examples, errors, and best practices.
Verification status: Consult the documentation, examples and any stated test scope below. How formula examples are checked
Availability and version history
- Introduced: LibreOffice 4.0.
- Availability: Supported in LibreOffice 26.8.1.1, verified against its function registry. This identifies a supported release, not a final working version.
- Scope: Desktop Calc, using the English function name. Availability in other spreadsheet applications is separate.
- Checked: 2026-10-14 — function reference; versioned support. Documentation/source check; not a new native test. Existing example-specific test claims below retain their stated scope.
Native-tested visual example: Regional service revenue
Quick answer: Sum amounts only when both region and category match. The following example was run in LibreOffice Calc 25.2.3.2, not simulated or inferred from a formula description.
Enter the sample data
The downloadable workbook uses these exact rows on its Example sheet. Enter the values or download the editable SUMIFS ODS practice workbook instead.
| Sheet row | A: Invoice | B: Region | C: Category | D: Revenue |
|---|---|---|---|---|
| 5 | INV-01 | North | Service | 120 |
| 6 | INV-02 | North | Product | 90 |
| 7 | INV-03 | South | Service | 75 |
| 8 | INV-04 | North | Service | 180 |
| 9 | INV-05 | West | Service | 60 |
| 10 | INV-06 | South | Product | 130 |
Formula and result
Select E5 in the practice workbook. The formula is:
=SUMIFS(D5:D10;B5:B10;"North";C5:C10;"Service")
Verified output: 300. Only INV-01 and INV-04 have Region = North and Category = Service (120 + 180).
Screenshot captured from LibreOffice Calc 25.2.3.2. The formula, input values and E5 result are visible in the actual application.
Second example you can try
The workbook also includes this independently tested variation in E12:
=SUMIFS(D5:D10;B5:B10;"South";C5:C10;"Product")
Verified output: 130. Change the related input cell and press Enter to see the result update.
Common mistake: The sum range and every criteria range must cover the same rows.
Official documentation: LibreOffice Calc reference for SUMIFS. Test scope: These two worked examples were recalculated in Calc 25.2.3.2; other historical formulas elsewhere on this page may still require separate testing. This file includes no generated or artificial application screenshot.
What the SUMIFS Function Does
- Adds values that satisfy two or more conditions
- Supports numeric, text, date, logical, and wildcard criteria
- Allows conditions across multiple ranges
- Requires all criteria ranges to be the same size
- Works efficiently on large datasets
- Ideal for multi‑condition reporting and analytics
SUMIFS is the multi‑condition extension of SUMIF.
Syntax
SUMIFS(sum_range; criteria_range1; criteria1; criteria_range2; criteria2; ...)
Basic Examples
Sum values where Region = “North” AND Sales > 1000
=SUMIFS(C1:C100; A1:A100; "North"; B1:B100; ">1000")
Sum values between 50 and 100
=SUMIFS(B1:B100; A1:A100; ">=50"; A1:A100; "<=100")
Sum values where Status = “Open” AND Priority = “High”
=SUMIFS(C1:C100; A1:A100; "Open"; B1:B100; "High")
Sum values where Date is in 2025 AND Category = “A”
=SUMIFS(C1:C100; A1:A100; ">="&DATE(2025;1;1); A1:A100; "<"&DATE(2026;1;1); B1:B100; "A")
Text & Wildcard Examples
Sum values where text starts with “A”
=SUMIFS(B1:B100; A1:A100; "A*")
Sum values where text contains “car”
=SUMIFS(B1:B100; A1:A100; "*car*")
Sum values where text ends with “ing”
=SUMIFS(B1:B100; A1:A100; "*ing")
Case‑sensitive conditional sum
SUMIFS is case‑insensitive.
For case‑sensitive logic, use SUMPRODUCT:
=SUMPRODUCT((EXACT(A1:A100; "Apple")) * B1:B100)
Advanced Examples
Use cell references as criteria
=SUMIFS(C1:C100; A1:A100; "=" & D1; B1:B100; ">=" & E1)
Sum values between two cell values
=SUMIFS(C1:C100; A1:A100; ">=" & D1; A1:A100; "<=" & E1)
Sum values where another column is not blank
=SUMIFS(C1:C100; A1:A100; "<>")
Sum values across sheets
=SUMIFS(Sheet1.C1:C100; Sheet1.A1:A100; "North")
+ SUMIFS(Sheet2.C1:C100; Sheet2.A1:A100; "North")
Sum errors (rare but possible)
=SUMIFS(B1:B100; A1:A100; "#N/A")
OR logic using SUMIFS
=SUMIFS(C1:C100; A1:A100; "North")
+ SUMIFS(C1:C100; A1:A100; "South")
Complex OR/AND logic using SUMPRODUCT
=SUMPRODUCT(((A1:A100="North") + (A1:A100="South")) * (B1:B100>1000) * C1:C100)
Common Errors and Fixes
Err:504 — Parameter error
Occurs when:
- Ranges are different sizes
- Criteria is malformed
- Semicolons are incorrect
SUMIFS 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)
SUMIFS is slow on large datasets
Use:
- Named ranges
- Helper columns
- Database functions (DSUM) for structured data
SUMIFS does not support OR logic natively
Use multiple SUMIFS or SUMPRODUCT.
Best Practices
- Ensure all ranges are the same size
- Always quote criteria containing operators
- Use cell references for dynamic criteria
- Use SUMIF for single‑condition logic
- Use SUMPRODUCT for advanced OR/AND combinations
- Clean imported data before applying criteria
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 |
=SUMIFS(B1:B5;C1:C5;"apple")
Expected result: 90.
Add amounts across several matching category rows.
If the result differs, check the referenced cells, argument separators and number formatting. For more information, consult the LibreOffice Calc official help.