AVERAGEIFS

Database Functions

(4.9/5)

Calculates the average of cells in a range that meet multiple criteria. More powerful than AVERAGEIF for complex conditional averaging.

Interactive Formula Tester

Parameters:

Formula:

=AVERAGEIFS("10,20,30,40,50,60,70,80,90,100, >0, >100")

Complete Theory & Understanding

Master the fundamentals of Excel AVERAGEIFS function

Core Concept

AVERAGEIFS is the enhanced version of AVERAGEIF, supporting multiple AND conditions. It calculates the average of cells that meet ALL specified criteria simultaneously. Perfect for complex filtering scenarios where you need to average data based on several conditions across different ranges.

Why Use AVERAGEIFS?

Average by multiple dimensions
Average by segments
Average in date ranges
Complex conditional averages

Key Characteristics

Multiple Criteria

Supports up to 127 criteria pairs

AVERAGEIFS(..., range1, crit1, range2, crit2)

AND Logic

All criteria must be satisfied

Meets ALL conditions

Flexible Operators

Same operators as AVERAGEIF

>, <, =, <>, *, ?

Required average_range

Must specify range to average

First parameter is always range

Function Anatomy

=AVERAGEIFS(parameters...)

Required

Parameters:

Function-specific parameters

Returns

Return Value:

Function-specific return type

Primary Use Cases

Multi-Dimensional Analysis

Average by multiple dimensions

Segmented Reporting

Average by segments

Time-Based Analysis

Average in date ranges

Advanced Filtering

Complex conditional averages

Theory Summary

Precise

Exact matching required

Position-Based

Returns numeric position

Error-Safe

Handles missing text gracefully

Syntax & Parameters

=AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

Required

average_range:

Range of cells to average

Required

criteria_range1:

First range to evaluate with criteria

Required

criteria1:

First criterion to apply

Optional

criteria_range2, criteria2, ...:

Additional ranges and criteria (up to 127 pairs)

Returns

Return Value:

Average of cells meeting all criteria

Description: Calculates average for cells meeting multiple criteria

Interactive Examples

Multiple Criteria

Average with 2 conditions

Input Text:

"Region="North", Year=2023"

Formula:

=AVERAGEIFS(Sales, Region, "North", Year, 2023)

Result:

Average North 2023 sales

Returns average meeting both criteria

VBA Implementation & Automation

Basic AVERAGEIFS in VBA

Simple VBA implementation

' Basic AVERAGEIFS in VBA
Sub AVERAGEIFSExample()
    Dim result As Variant
    result = Application.WorksheetFunction.AverageIfs(Range("C1:C100"), _
        Range("A1:A100"), "North", Range("B1:B100"), 2023)
    MsgBox "Average: " & result
End Sub

' Dynamic criteria
Sub AverageByMultipleCriteria()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim avgRange As Range
    Dim crit1Range As Range
    Dim crit2Range As Range
    
    Set avgRange = ws.Range("Sales")
    Set crit1Range = ws.Range("Region")
    Set crit2Range = ws.Range("Year")
    
    ws.Range("Result").Value = Application.AverageIfs(avgRange, _
        crit1Range, "North", crit2Range, 2023)
End Sub

' Complex report
Sub BuildComplexReport()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim regions() As String
    Dim i As Integer
    
    regions = Array("North", "South", "East", "West")
    
    For i = 0 To UBound(regions)
        ws.Cells(i + 1, 1).Value = regions(i)
        ws.Cells(i + 1, 2).Value = Application.AverageIfs(ws.Range("Sales"), _
            ws.Range("Region"), regions(i), ws.Range("Year"), 2023)
    Next i
End Sub