SUMIF Function (LibreOffice Calc)
The SUMIF function in LibreOffice Calc adds values 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 SUMIF Function Does ▾
- Adds values that match a single condition
- Supports numeric, text, date, and wildcard criteria
- Allows separate criteria and sum ranges
- Works efficiently on large datasets
- Ideal for conditional reporting and filtering
SUMIF is the conditional version of SUM.
Syntax ▾
SUMIF(range; criteria; sum_range)
If
sum_range is omitted, Calc sums the range itself.
Criteria containing operators or text must be in quotes.
Basic Examples ▾
Sum values greater than 50
=SUMIF(A1:A10; ">50")
Sum values in B1:B10 where A1:A10 equals “North”
=SUMIF(A1:A10; "North"; B1:B10)
Sum values equal to a specific number
=SUMIF(A1:A10; 100)
Sum values not equal to text
=SUMIF(A1:A10; "<>North"; B1:B10)
Text & Wildcard Examples ▾
Sum values where text starts with “A”
=SUMIF(A1:A10; "A*"; B1:B10)
Sum values where text ends with “ing”
=SUMIF(A1:A10; "*ing"; B1:B10)
Sum values where text contains “car”
=SUMIF(A1:A10; "*car*"; B1:B10)
Sum values with exactly 5 characters
=SUMIF(A1:A10; "?????"; B1:B10)
Wildcards work only with text conditions.
Advanced Examples ▾
Use a cell reference as the criteria
=SUMIF(A1:A10; "=" & D1; B1:B10)
Sum values between 50 and 100
=SUMIF(A1:A10; ">=50"; B1:B10) - SUMIF(A1:A10; ">100"; B1:B10)
Sum dates before a specific date
=SUMIF(A1:A10; "<" & DATE(2025;1;1); B1:B10)
Sum values where another column is not blank
=SUMIF(A1:A10; "<>"; B1:B10)
Sum values across sheets
=SUMIF(Sheet1.A1:A10; ">0"; Sheet1.B1:B10)
+ SUMIF(Sheet2.A1:A10; ">0"; Sheet2.B1:B10)
Case‑sensitive conditional sum
SUMIF is case‑insensitive.
For case‑sensitive logic, use SUMPRODUCT:
=SUMPRODUCT((EXACT(A1:A10; "Apple")) * B1:B10)
Sum errors (rare but possible)
=SUMIF(A1:A10; "#N/A"; B1:B10)
Common Errors and Fixes ▾
SUMIF returns 0 unexpectedly
Possible causes:
- Criteria missing quotes
- Numbers stored as text
- Hidden spaces or non‑breaking spaces
- Wildcards used incorrectly
sum_rangeandrangesizes differ
Fix:
Check with:
=LEN(A1)
Err:504 — Parameter error
Occurs when:
- Ranges are different sizes
- Criteria is malformed
- Semicolons are incorrect
SUMIF is slow on large datasets
Use:
- Named ranges
- Helper columns
- SUMIFS (more optimized)
SUMIF does not support OR logic
Use:
=SUMIF(A1:A10; "North"; B1:B10)
+ SUMIF(A1:A10; "South"; B1:B10)
Or use SUMPRODUCT for complex OR logic.
Best Practices ▾
- Always quote criteria containing operators
- Use SUMIFS for multi‑condition logic
- Use cell references for dynamic criteria
- Clean imported data before applying criteria
- Ensure
rangeandsum_rangeare the same size - Use wildcards for flexible text matching
SUMIF is essential for financial models, dashboards, and conditional reporting.