Excel Function Translator Logo
    The Excel Translator

    AVERAGEIF

    Statistical

    Calculates the average of cells that meet a specified condition.

    Translations
    EnglishAVERAGEIF
    FrenchMOYENNE.SI
    SpanishPROMEDIO.SI
    GermanMITTELWERTWENN
    ItalianMEDIA.SE
    PortugueseMÉDIASE
    DutchGEMIDDELDE.ALS
    PolishŚREDNIA.JEŻELI
    RussianСРЗНАЧЕСЛИ
    TurkishORTALAMAEĞER
    CzechAVERAGEIF
    HungarianÁTLAGHA
    SwedishMEDEL.OM
    DanishMIDDEL.HVIS
    FinnishKESKIARVO.JOS
    Syntax
    AVERAGEIF(range, criteria, [average_range])

    Arguments

    • rangeThe range to evaluate
    • criteriaThe condition to meet
    • average_rangeThe range to average (defaults to range)(optional)
    Examples
    =AVERAGEIF(A1:A10,">0")
    Avg of positives

    Averages only positive values

    =AVERAGEIF(A:A,"Sales",B:B)
    Avg for Sales

    Averages B where A is 'Sales'

    =AVERAGEIF(A:A,"<>")
    Avg non-empty

    Averages only non-empty cells

    Tips & Best Practices
    • •Use AVERAGEIFS for multiple conditions
    • •Ignores empty cells in average_range
    • •Returns #DIV/0! if no cells match
    Common Mistakes
    • •Forgetting that average_range is optional and defaults to the same range being tested - omitting it when you actually meant to average a different column produces a misleading but valid-looking result
    • •Using text criteria without quotes, like AVERAGEIF(range,>100,avg_range) instead of AVERAGEIF(range,">100",avg_range), which raises a formula error
    • •Not accounting for cells that don't meet the criteria at all, resulting in a #DIV/0! error when zero cells match and there's nothing to average
    Related Functions
    AVERAGEIFSExtends AVERAGEIF to support multiple criteria across multiple ranges instead of just one condition.
    SUMIFSums matching values instead of averaging them, using the same single-criterion structure.
    COUNTIFCounts matching cells, useful for sanity-checking that AVERAGEIF's #DIV/0! isn't caused by zero matches.
    Frequently Asked Questions

    Why does AVERAGEIF return #DIV/0!?

    No cells matched your criteria, so there's nothing to average - check that the criteria range and the values actually overlap the way you expect.

    Do I need to specify average_range if it's the same as the criteria range?

    No, it's optional - if omitted, AVERAGEIF averages the same range it evaluates the criteria against.

    How do I average based on more than one condition?

    Use AVERAGEIFS instead, which accepts multiple criteria-range pairs rather than the single condition AVERAGEIF supports.

    Need to translate a formula using AVERAGEIF?

    Use our translator to convert your complete formula