SUMIFS Function (OpenOffice Calc)

• 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.

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

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.

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")

• 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