Cellwise

Conditional function

Excel AVERAGEIFS function

AVERAGEIFS averages a column, but only for rows where every condition is true. Use it for the average hours of one team on one shift, or the average deal size in a region above a minimum.

fx=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)

Arguments

average_range
The cells to average.
criteria_range1
The first range to test.
criteria1
The condition for that range, like "Night" or ">=4".

AVERAGEIFS examples

Every result below was calculated live from this sample sheet.

ABCD
1EmployeeTeamShiftHours
2AnaPackingDay7.5
3BenPackingNight9
4CalShippingDay8
5DeePackingNight10.5
6EliShippingNight6
7FayPackingDay8.5
  1. fx=AVERAGEIFS(D2:D7,B2:B7,"Packing",C2:C7,"Night")

    Result: 9.75

    Average hours for the Packing team on night shift.

  2. fx=AVERAGEIFS(D2:D7,C2:C7,"Day")

    Result: 8

    One condition is fine: the average day-shift length.

  3. fx=AVERAGEIFS(D2:D7,B2:B7,"Packing",D2:D7,">=8")

    Result: 9.3333333333

    Average of Packing shifts that ran 8 hours or more.

  4. fx=AVERAGEIFS(D2:D7,B2:B7,"Shipping",D2:D7,">10")

    Result: #DIV/0!

    No Shipping shift ran over 10 hours, so this returns #DIV/0!.

How to use AVERAGEIFS

  1. Click an empty cell.
  2. Type =AVERAGEIFS( and select the column to average.
  3. Add a comma, the first range to test, a comma and its condition.
  4. Add more range, condition pairs as needed.
  5. Close the bracket and press Enter.

Common AVERAGEIFS mistakes

Putting the average range last

That is AVERAGEIF order. In AVERAGEIFS the range to average comes first.

No rows match every condition

AVERAGEIFS returns #DIV/0! when nothing matches. Wrap it in IFERROR for a clean result.

Ranges of different sizes

All ranges must have the same shape, or Excel returns #VALUE!.

AVERAGEIFS FAQ

What is the difference between AVERAGEIF and AVERAGEIFS?

AVERAGEIF takes one condition and puts the average range last. AVERAGEIFS takes one or more conditions and puts the average range first.

How do I average between two values in Excel?

Use the same range twice: =AVERAGEIFS(D2:D50,D2:D50,">=10",D2:D50,"<=20").

Why is AVERAGEIFS giving #DIV/0!?

No row meets all the conditions at once. Test each condition alone with COUNTIFS to find the one that never matches.

Related functions

Updated 2026-10-31