AVERAGEIF and AVERAGEIFS in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

This table shows names of students in column A and their test scores in column B.

Cell	Value
A1	Name
B1	Score
A2	Alice
B2	85
A3	Bob
B3	90
A4	Alice
B4	78
A5	Charlie
B5	92
A6	Bob
B6	88
A7	Alice
B7	95

Formula Trace

=AVERAGEIF(A2:A7, "Alice", B2:B7)

Step 1: Check each cell in A2:A7 if it equals "Alice"

Step 2: Select scores from B2:B7 where corresponding A cells are TRUE

Step 3: Calculate average of [85, 78, 95]

Cell Reference Map

    A       B
1 |Name   |Score
2 |Alice  | 85  
3 |Bob    | 90  
4 |Alice  | 78  
5 |Charlie| 92  
6 |Bob    | 88  
7 |Alice  | 95  

Formula references:
- A2:A7 for criteria range
- B2:B7 for average range

The formula looks at names in A2:A7 to find 'Alice' and averages the matching scores in B2:B7.

Result

    A       B       C
1 |Name   |Score  | Result
2 |Alice  | 85    | 86
3 |Bob    | 90    |
4 |Alice  | 78    |
5 |Charlie| 92    |
6 |Bob    | 88    |
7 |Alice  | 95    |

The result 86 is the average score of all rows where the name is Alice.

Sheet Trace Quiz - 3 Questions

What does the formula =AVERAGEIF(A2:A7, "Alice", B2:B7) do?

A It averages scores in B2:B7 only for rows where A2:A7 is 'Alice'.

B It averages all scores in B2:B7 regardless of names.

C It counts how many times 'Alice' appears in A2:A7.

D It sums all scores in B2:B7 for 'Bob'.

Key Result

AVERAGEIF averages values in one range based on a single condition in another range.