AVERAGEIFS Function (OpenOffice Calc)

• Calculates averages based on 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 text and empty cells in the average range

AVERAGEIFS is essential for multi‑criteria statistical analysis.

AVERAGEIFS(average_range; criteria_range1; criteria1; criteria_range2; criteria2; ...)

Arguments:

• average_range — The cells to average
• criteria_range1 — The first range to evaluate
• criteria1 — The first condition
• criteria_range2; criteria2; … — Additional range/condition pairs

AVERAGEIFS supports:

• * — matches any sequence of characters
• ? — matches any single character
• ~ — escapes literal * or ?

Examples:

• "A*" matches any text starting with A
• "*Service*" matches text containing “Service”
• "???" matches any 3‑character string
• "~*" matches a literal asterisk

Average values that meet two conditions

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

Average values matching text and numeric criteria

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

Average values using wildcard matching

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

Average values using cell‑based criteria

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

If:

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

…the formula uses those conditions dynamically.

Average values across sheets

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

Average values within a date range

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

Average values not equal to something

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

Average values with partial text match

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

Average values using numeric thresholds

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

OR logic workaround

OpenOffice Calc does not support OR conditions directly in AVERAGEIFS.
Use two AVERAGEIFS and average them manually:

=(AVERAGEIFS(C1:C100; A1:A100; "North") +
  AVERAGEIFS(C1:C100; A1:A100; "South")) / 2

Average values excluding zeros

=AVERAGEIFS(A1:A100; A1:A100; "<>0")

• Ensure all criteria ranges are the same size
• Use cell references for dynamic criteria
• Use wildcards for flexible text matching
• Clean imported data to remove hidden spaces
• Use helper columns for complex logic
• Use named ranges for cleaner formulas