AVERAGEIFS Function (LibreOffice Calc)
The AVERAGEIFS function in LibreOffice Calc calculates the average of values that meet multiple conditions across one or more ranges. This guide explains syntax, operators, wildcards, examples, errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✖ |
| Wps | ✔ |
| Zoho | ✔ |
What the AVERAGEIFS Function Does â–¾
- Averages values that satisfy two or more conditions
- Supports numeric, text, date, logical, and wildcard criteria
- Allows conditions across multiple ranges
- Requires all criteria ranges to be the same size
- Ignores empty cells automatically
- Ideal for multi‑condition reporting and analytics
AVERAGEIFS is the multi‑condition extension of AVERAGEIF.
Syntax â–¾
AVERAGEIFS(average_range; criteria_range1; criteria1; criteria_range2; criteria2; ...)
The average_range must come first, unlike AVERAGEIF.
All criteria ranges must be the same size.
Basic Examples â–¾
Average values where Region = “North” AND Sales > 1000
=AVERAGEIFS(C1:C100; A1:A100; "North"; B1:B100; ">1000")
Average values between 50 and 100
=AVERAGEIFS(B1:B100; A1:A100; ">=50"; A1:A100; "<=100")
Average values where Status = “Open” AND Priority = “High”
=AVERAGEIFS(C1:C100; A1:A100; "Open"; B1:B100; "High")
Average values where Date is in 2025 AND Category = “A”
=AVERAGEIFS(C1:C100; A1:A100; ">=2025-01-01"; A1:A100; "<=2025-12-31"; B1:B100; "A")
Text & Wildcard Examples â–¾
Average values where text starts with “A”
=AVERAGEIFS(B1:B100; A1:A100; "A*")
Average values where text contains “car”
=AVERAGEIFS(B1:B100; A1:A100; "*car*")
Average values where text ends with “ing”
=AVERAGEIFS(B1:B100; A1:A100; "*ing")
Case‑sensitive conditional average
AVERAGEIFS is case‑insensitive.
For case‑sensitive logic, use SUMPRODUCT:
=SUMPRODUCT((EXACT(A1:A100; "Apple")) * B1:B100)
/ SUMPRODUCT(EXACT(A1:A100; "Apple"))
Advanced Examples â–¾
Use cell references as criteria
=AVERAGEIFS(C1:C100; A1:A100; "=" & D1; B1:B100; ">=" & E1)
Average values between two cell values
=AVERAGEIFS(C1:C100; A1:A100; ">=" & D1; A1:A100; "<=" & E1)
Average values where another column is not blank
=AVERAGEIFS(C1:C100; A1:A100; "<>")
Average across sheets
=AVERAGEIFS(Sheet1.C1:C100; Sheet1.A1:A100; "North")
+ AVERAGEIFS(Sheet2.C1:C100; Sheet2.A1:A100; "North")
Average errors (rare but possible)
=AVERAGEIFS(B1:B100; A1:A100; "#N/A")
OR logic using AVERAGEIFS
=AVERAGEIFS(C1:C100; A1:A100; "North")
+ AVERAGEIFS(C1:C100; A1:A100; "South")
Complex OR/AND logic using SUMPRODUCT
=SUMPRODUCT(((A1:A100="North") + (A1:A100="South")) * (B1:B100>1000) * C1:C100)
/ SUMPRODUCT(((A1:A100="North") + (A1:A100="South")) * (B1:B100>1000))
Common Errors and Fixes â–¾
Err:504 — Parameter error
Occurs when:
- Ranges are different sizes
- Criteria is malformed
- Semicolons are incorrect
AVERAGEIFS returns 0 unexpectedly
Possible causes:
- Criteria missing quotes
- Numbers stored as text
- Hidden spaces or non‑breaking spaces
- Wildcards used incorrectly
Fix:
Check with:
=LEN(A1)
AVERAGEIFS returns #DIV/0!
Occurs when:
- No values match the conditions
- All matching values are empty
AVERAGEIFS is slow on large datasets
Use:
- Named ranges
- Helper columns
- Database functions (DAVERAGE) for structured data
AVERAGEIFS does not support OR logic natively
Use multiple AVERAGEIFS or SUMPRODUCT.
Best Practices â–¾
- Ensure all ranges are the same size
- Always quote criteria containing operators
- Use cell references for dynamic criteria
- Use AVERAGEIF for single‑condition logic
- Use SUMPRODUCT for advanced OR/AND combinations
- Clean imported data before applying criteria
AVERAGEIFS is essential for dashboards, KPI tracking, and multi‑condition financial models.