Value aggregation (SUM, COUNT, AVG) in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

This data shows sales amounts in cells A2 to A5. We want to calculate the total sales, count of sales entries, and average sales.

Cell	Value
A1	Sales
A2	100
A3	200
A4	150
A5	50
A6	Total
B1	Count
B6	4
C1	Average
C6	125

Formula Trace

=SUM(A2:A5)

Step 1: A2:A5

Step 2: SUM(100, 200, 150, 50)

Cell Reference Map

    A    B    C
1 |Sales|Count|Average
2 | 100 |     |      
3 | 200 |     |      
4 | 150 |     |      
5 |  50 |     |      
6 |Total|  4  | 125  

The formula references cells A2 to A5 which contain the sales values to sum.

Result

    A    B    C
1 |Sales|Count|Average
2 | 100 |     |      
3 | 200 |     |      
4 | 150 |     |      
5 |  50 |     |      
6 | 500 |  4  | 125  

The sum of sales values (100 + 200 + 150 + 50) is 500, shown in cell A6. The count of sales entries is 4 in B6, and the average sales is 125 in C6.

Sheet Trace Quiz - 3 Questions

What does the formula =SUM(A2:A5) calculate?

A The total of all sales values in A2 to A5

B The number of sales entries in A2 to A5

C The average of sales values in A2 to A5

D The highest sales value in A2 to A5

Key Result

SUM(range) adds all numbers in the specified range to get a total.

Practice

1. What does the SUM function do in Google Sheets?

easy

A. Adds all numbers in a selected range

B. Counts how many cells have numbers

C. Finds the highest number in a range

D. Calculates the average of numbers

Solution

1. Step 1: Understand the purpose of SUM

   The SUM function adds all numeric values in the specified range.

2. Step 2: Compare with other functions

   COUNT counts numbers, AVERAGE calculates mean, MAX finds highest number.

3. Final Answer:

   Adds all numbers in a selected range -> Option A

4. Quick Check:

   SUM = addition [OK]

Hint: SUM adds numbers; think of adding bills [OK]

Common Mistakes:

• Confusing SUM with COUNT
• Thinking SUM finds average
• Using SUM for text values

2. Which of these is the correct syntax to count numbers in range A1:A5?

easy

A. =COUNTNUM(A1:A5)

B. =SUM(A1:A5)

C. =COUNT(A1:A5)

D. =AVERAGE(A1:A5)

Solution

1. Step 1: Select the function that counts numeric cells

   COUNT is the correct function to count numeric cells in a range.

2. Step 2: Check syntax correctness

   =COUNT(A1:A5) is valid; others are either wrong function or invalid syntax.

3. Final Answer:

   =COUNT(A1:A5) -> Option C

4. Quick Check:

   COUNT counts numbers [OK]

Hint: COUNT counts numbers, SUM adds, AVERAGE averages [OK]

Common Mistakes:

• Using SUM instead of COUNT
• Typing COUNTNUM which doesn't exist
• Missing equal sign

3. Given cells A1=5, A2=10, A3=15, what is the result of =AVERAGE(A1:A3)?

medium

A. 30

B. 15

C. 5

D. 10

Solution

1. Step 1: Sum the values in A1 to A3

   5 + 10 + 15 = 30

2. Step 2: Divide sum by count of numbers

   There are 3 numbers, so 30 ÷ 3 = 10

3. Final Answer:

   10 -> Option D

4. Quick Check:

   Average = sum/count = 10 [OK]

Hint: Average = total sum divided by count [OK]

Common Mistakes:

• Adding but not dividing
• Dividing by wrong count
• Confusing with SUM result

4. You wrote =SUM(A1:A5 but get an error. What is the fix?

medium

A. Change SUM to COUNT

B. Add a closing parenthesis: =SUM(A1:A5)

C. Remove the equal sign

D. Add a comma after A5

Solution

1. Step 1: Check the formula for balanced parentheses

   The formula is missing a closing parenthesis which causes an error.

2. Step 2: Correct the formula syntax

   Adding the closing parenthesis fixes the error: =SUM(A1:A5)

3. Final Answer:

   Add a closing parenthesis: =SUM(A1:A5) -> Option B

4. Quick Check:

   Parentheses must be balanced [OK]

Hint: Always close parentheses in formulas [OK]

Common Mistakes:

• Forgetting closing parenthesis
• Changing function unnecessarily
• Removing equal sign

5. You have numbers in B1:B6: 4, 8, 12, 16, 20, and a text cell (e.g., 'N/A'). Which formula gives the average of only the numbers?

hard

A. =SUM(B1:B6)/COUNT(B1:B6)

B. =SUM(B1:B6)/COUNTA(B1:B6)

C. =SUM(B1:B6)/6

D. =COUNT(B1:B6)/SUM(B1:B6)

Solution

1. Step 1: Understand how COUNT and COUNTA handle text

   COUNT counts only numeric cells (ignores text), COUNTA counts non-empty cells (includes text).

2. Step 2: Check each formula

   A uses COUNTA (divides by 6, wrong); B uses COUNT (divides by 5, correct); C hardcodes /6 (wrong); D reverses operation.

3. Final Answer:

   =SUM(B1:B6)/COUNT(B1:B6) -> Option A

4. Quick Check:

   Average = sum ÷ count of numbers [OK]

Hint: Divide sum by count to get average safely [OK]

Common Mistakes:

• Using COUNTA instead of COUNT
• Dividing by total cells including non-numbers
• Reversing sum and count