AVERAGEIF and AVERAGEIFS in Excel - Cell-by-Cell Formula Trace

Sample Data

Sales data for products with their categories and sales amounts.

Cell	Value
A1	Product
B1	Category
C1	Sales
A2	Apple
B2	Fruit
C2	100
A3	Banana
B3	Fruit
C3	150
A4	Carrot
B4	Vegetable
C4	80
A5	Broccoli
B5	Vegetable
C5	120
A6	Orange
B6	Fruit
C6	130
A7	Spinach
B7	Vegetable
C7	90

Formula Trace

=AVERAGEIF(B2:B7, "Fruit", C2:C7)

Step 1: B2:B7 = {"Fruit", "Fruit", "Vegetable", "Vegetable", "Fruit", "Vegetable"}

Step 2: Criteria "Fruit" matches positions 1, 2, and 5 in B2:B7

Step 3: C2:C7 = {100, 150, 80, 120, 130, 90}

Step 4: Select sales values where category is "Fruit": {100, 150, 130}

Step 5: Calculate average of {100, 150, 130}

Cell Reference Map

    A       B          C
1 Product  Category   Sales
2 Apple    Fruit  -->  100
3 Banana   Fruit  -->  150
4 Carrot   Vegetable  80
5 Broccoli Vegetable 120
6 Orange   Fruit  -->  130
7 Spinach  Vegetable  90

Arrows show which sales values are included for "Fruit" category.

The formula checks categories in B2:B7 and picks sales from C2:C7 where category is "Fruit".

Result

    A       B          C          D
1 Product  Category   Sales     Result
2 Apple    Fruit      100      126.67
3 Banana   Fruit      150
4 Carrot   Vegetable  80
5 Broccoli Vegetable  120
6 Orange   Fruit      130
7 Spinach  Vegetable  90

Cell D2 contains the formula =AVERAGEIF(B2:B7, "Fruit", C2:C7) and shows 126.67

The average sales for products in the "Fruit" category is 126.67, shown in D2.

Sheet Trace Quiz - 3 Questions

Which sales values are included in the average when using =AVERAGEIF(B2:B7, "Fruit", C2:C7)?

A 100, 150, 130

B 80, 120, 90

C All sales values

D Only 100 and 150

Key Result

AVERAGEIF averages values in a range based on one condition; AVERAGEIFS averages values based on multiple conditions.