Browse

SUMIF Function (LibreOffice Calc)

Math Intermediate LibreOffice Calc LibreOffice 3.0

The SUMIF function in LibreOffice Calc adds values that meet a single condition. This guide explains syntax, operators, wildcards, examples, errors, and best practices.

Verification status: Consult the documentation and examples below. Native application testing has not been documented for this guide. How formula examples are checked

The **SUMIF** function adds values that meet a **single condition**. It is one of the most widely used conditional functions in LibreOffice Calc, ideal for financial analysis, reporting, filtering, and data cleanup.

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_range and range sizes 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 range and sum_range are the same size
  • Use wildcards for flexible text matching
SUMIF is essential for financial models, dashboards, and conditional 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 
=SUMIF(C1:C5;"pear";B1:B5)

Expected result: 60.

Add amounts from rows where the category is pear.

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