Cellwise

Conditional function

Excel AVERAGEIF function

AVERAGEIF averages the numbers in rows that meet one condition. Use it for the average score of one class, the average ticket in one category, or to average while ignoring zeros.

fx=AVERAGEIF(range, criteria, [average_range])

Arguments

range
The cells to test against the condition.
criteria
The condition, like "Math" or ">0". Text and operators go in quotes.
average_range
Optional. The cells to average. Leave it out to average the range itself.

AVERAGEIF examples

Every result below was calculated live from this sample sheet.

ABC
1StudentSubjectScore
2LiamMath82
3LiamScience74
4ZoeMath91
5ZoeScience0
6NoahMath68
7NoahScience88
  1. fx=AVERAGEIF(B2:B7,"Math",C2:C7)

    Result: 80.333333333

    The average Math score across all three students.

  2. fx=AVERAGEIF(C2:C7,">0")

    Result: 80.6

    Zoe's 0 is a missing test. ">0" averages only real scores.

  3. fx=AVERAGEIF(A2:A7,"Zoe",C2:C7)

    Result: 45.5

    Zoe's average, including the 0, which drags it down.

  4. fx=AVERAGEIF(B2:B7,"Art",C2:C7)

    Result: #DIV/0!

    No rows match "Art", so there is nothing to average and you get #DIV/0!.

How to use AVERAGEIF

  1. Click an empty cell.
  2. Type =AVERAGEIF( and select the column to test.
  3. Type a comma and the condition in quotes.
  4. Type a comma and select the column to average.
  5. Close the bracket and press Enter.

Common AVERAGEIF mistakes

No matching rows

If nothing meets the condition, AVERAGEIF returns #DIV/0!. Wrap it in IFERROR to show a blank or message.

Zeros counted as scores

Zeros are included in the average. Use a criteria like ">0", or switch to AVERAGEIFS to add that as a second condition.

Mixing up argument order with AVERAGEIFS

AVERAGEIF puts average_range last. AVERAGEIFS puts it first.

AVERAGEIF FAQ

How do I average excluding zeros in Excel?

Use =AVERAGEIF(range,"<>0"), or ">0" if you also want to skip negatives.

Why does AVERAGEIF return #DIV/0!?

No cells met the condition, or the matching cells hold no numbers. Check the spelling of your criteria.

Does AVERAGEIF ignore blank cells?

Yes. Blank cells in the average range are skipped and do not count as zeros.

Practice AVERAGEIF in these lessons

Guides that use AVERAGEIF

Related functions

Updated 2026-10-31