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.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✖ |
| Wps | ✔ |
| Zoho | ✔ |
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; ...)
The sum_range must come first, unlike SUMIF.
All criteria ranges must be the same size.
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; ">=2025-01-01"; A1:A100; "<=2025-12-31"; 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
SUMIFS is essential for dashboards, KPI tracking, and multi‑condition financial models.