Contact
12 programs
Explore the site

SUMIFS Function (OpenOffice Calc)

Math Intermediate OpenOffice Calc OpenOffice.org 4.0

The SUMIFS function in OpenOffice Calc adds values that meet multiple conditions. Learn syntax, examples, common 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 SUMIFS function in OpenOffice Calc allows you to add values that meet multiple conditions simultaneously. It is ideal for filtering totals by region, date ranges, categories, thresholds, or any combination of criteria. When you need precise, multi‑layered filtering, SUMIFS is the most powerful conditional summation tool available in OpenOffice Calc.

What the SUMIFS Function Does

  • Adds values that meet two or more conditions
  • Supports numeric, text, date, and logical criteria
  • Allows comparison operators (>, <, >=, <>, etc.)
  • Supports wildcard matching (* and ?)
  • Evaluates each condition across aligned ranges
  • Works across sheets
  • Ignores empty cells automatically

SUMIFS is essential for dashboards, financial models, inventory systems, and any dataset requiring multi‑criteria filtering.

Syntax

SUMIFS(sum_range; criteria_range1; criteria1; criteria_range2; criteria2; ...)

Arguments:

  • sum_range — The cells to add
  • criteria_range1 — The first range to evaluate
  • criteria1 — The first condition
  • criteria_range2; criteria2; … — Additional range/condition pairs
All criteria ranges must be the same size as sum_range.

Basic Examples

Sum values that meet two conditions

=SUMIFS(C1:C100; A1:A100; "North"; B1:B100; ">1000")

Adds values in C1:C100 where:

  • Column A equals "North"
  • Column B is greater than 1000

Sum values based on text and numbers

=SUMIFS(B1:B50; A1:A50; "Service"; C1:C50; "<=500")

Sum values using wildcard matching

=SUMIFS(C1:C200; A1:A200; "Jan*"; B1:B200; ">0")

Matches any text beginning with “Jan”.

Sum values using cell‑based criteria

=SUMIFS(C1:C100; A1:A100; E1; B1:B100; E2)

If:

  • E1 contains "North"
  • E2 contains ">500"

…the formula uses those conditions dynamically.

Advanced Examples

Sum values across sheets

=SUMIFS(Sheet1.C1:C500; Sheet1.A1:A500; "West"; Sheet1.B1:B500; "<100")

Sum values within a date range

=SUMIFS(C1:C1000; A1:A1000; ">=" & DATE(2025;1;1); A1:A1000; "<=" & DATE(2025;12;31))

Sum values not equal to something

=SUMIFS(C1:C100; A1:A100; "<>North"; B1:B100; ">0")

Sum values with partial text match

=SUMIFS(C1:C100; A1:A100; "*Service*"; B1:B100; ">100")

Sum values using numeric thresholds

=SUMIFS(D1:D200; B1:B200; ">50"; C1:C200; "<100")

Sum values with OR logic (workaround)

OpenOffice Calc does not support OR conditions directly in SUMIFS, but you can combine two SUMIFS:

=SUMIFS(C1:C100; A1:A100; "North") +
  SUMIFS(C1:C100; A1:A100; "South")

Common Errors and Fixes

Err:508 — Missing parenthesis

Usually caused by:

  • Incorrect concatenation of criteria
  • Missing )
  • Using commas instead of semicolons

Err:504 — Parameter error

Occurs when:

  • sum_range and criteria ranges are different sizes
  • A malformed condition is used
  • A text condition is missing quotes

SUMIFS returns 0 unexpectedly

Possible causes:

  • Text numbers in the range
  • Hidden apostrophes ('123)
  • Criteria not matching due to spacing or case
  • Wildcards not used correctly
  • Date criteria not constructed properly

Fix: Convert text to numbers:
Data → Text to Columns → OK

Best Practices

  • Keep all ranges the same size
  • Use cell references for criteria to make formulas dynamic
  • Use wildcards for flexible text matching
  • Avoid mixing text and numbers in the same column
  • Use helper columns for complex logic
  • Use named ranges for cleaner formulas
For multi‑criteria dashboards, store each condition in its own cell and reference them in SUMIFS.
Example: =SUMIFS(Sales; Region; F1; Category; F2; Quarter; F3)