AVERAGEIF Function (LibreOffice Calc)
The AVERAGEIF function in LibreOffice Calc calculates the average of values that meet a single condition. This guide explains syntax, operators, wildcards, examples, errors, and best practices.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the AVERAGEIF Function Does ▾
- Averages values that match a single condition
- Supports numeric, text, date, and wildcard criteria
- Allows separate criteria and average ranges
- Ignores empty cells automatically
- Works efficiently on large datasets
- Ideal for conditional reporting and analytics
AVERAGEIF is the conditional version of AVERAGE.
Syntax ▾
AVERAGEIF(range; criteria; average_range)
If
average_range is omitted, Calc averages the range itself.
Criteria containing operators or text must be in quotes.
Basic Examples ▾
Average values greater than 50
=AVERAGEIF(A1:A10; ">50")
Average values in B1:B10 where A1:A10 equals “North”
=AVERAGEIF(A1:A10; "North"; B1:B10)
Average values equal to a specific number
=AVERAGEIF(A1:A10; 100)
Average values not equal to text
=AVERAGEIF(A1:A10; "<>North"; B1:B10)
Text & Wildcard Examples ▾
Average values where text starts with “A”
=AVERAGEIF(A1:A10; "A*"; B1:B10)
Average values where text contains “car”
=AVERAGEIF(A1:A10; "*car*"; B1:B10)
Average values where text ends with “ing”
=AVERAGEIF(A1:A10; "*ing"; B1:B10)
Average values with exactly 5 characters
=AVERAGEIF(A1:A10; "?????"; B1:B10)
Wildcards work only with text conditions.
Advanced Examples ▾
Use a cell reference as the criteria
=AVERAGEIF(A1:A10; "=" & D1; B1:B10)
Average values between 50 and 100
=AVERAGEIF(A1:A10; ">=50"; B1:B10) - AVERAGEIF(A1:A10; ">100"; B1:B10)
Average dates before a specific date
=AVERAGEIF(A1:A10; "<" & DATE(2025;1;1); B1:B10)
Average values where another column is not blank
=AVERAGEIF(A1:A10; "<>"; B1:B10)
Case‑sensitive conditional average
AVERAGEIF is case‑insensitive.
For case‑sensitive logic, use SUMPRODUCT:
=SUMPRODUCT((EXACT(A1:A10; "Apple")) * B1:B10) / SUMPRODUCT(EXACT(A1:A10; "Apple"))
Average across sheets
=AVERAGEIF(Sheet1.A1:A10; ">0"; Sheet1.B1:B10)
+ AVERAGEIF(Sheet2.A1:A10; ">0"; Sheet2.B1:B10)
Average errors (rare but possible)
=AVERAGEIF(A1:A10; "#N/A"; B1:B10)
Common Errors and Fixes ▾
AVERAGEIF returns 0 unexpectedly
Possible causes:
- Criteria missing quotes
- Numbers stored as text
- Hidden spaces or non‑breaking spaces
- Wildcards used incorrectly
rangeandaverage_rangesizes differ
Fix:
Check with:
=LEN(A1)
Err:504 — Parameter error
Occurs when:
- Ranges are different sizes
- Criteria is malformed
- Semicolons are incorrect
AVERAGEIF returns #DIV/0!
Occurs when:
- No values match the condition
- All matching values are empty
AVERAGEIF is slow on large datasets
Use:
- Named ranges
- Helper columns
- AVERAGEIFS (more optimized)
Best Practices ▾
- Always quote criteria containing operators
- Use AVERAGEIFS for multi‑condition logic
- Use cell references for dynamic criteria
- Clean imported data before applying criteria
- Ensure
rangeandaverage_rangeare the same size - Use wildcards for flexible text matching
AVERAGEIF is essential for conditional reporting, KPI analysis, and financial modeling.