Value aggregation (SUM, COUNT, AVG) in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to sum the values in cells A1 to A5.

Google Sheets

=SUM([1])

Drag options to blanks, or click blank then click option'

AA1-A5

BA1;A5

CA1,A5

DA1:A5

Attempts:

3 left

💡 Hint

Common Mistakes

Using a dash '-' instead of a colon ':' for ranges.

Separating cells with commas or semicolons instead of a colon.

The SUM function adds all numbers in the range A1 to A5. The correct range syntax is A1:A5.

2fill in blank

medium

Complete the formula to count how many numbers are in cells B1 to B10.

Google Sheets

=COUNT([1])

Drag options to blanks, or click blank then click option'

AB1:B10

BB1;B10

CB1-B10

DB1,B10

Attempts:

3 left

💡 Hint

Common Mistakes

Using commas or semicolons instead of a colon for ranges.

Using a dash '-' which is not valid for ranges.

The COUNT function counts numeric values in the range B1 to B10. The correct range syntax is B1:B10.

3fill in blank

hard

Fix the error in the formula to calculate the average of cells C1 to C7.

Google Sheets

=AVERAGE([1])

Drag options to blanks, or click blank then click option'

AC1-C7

BC1;C7

CC1:C7

DC1,C7

Attempts:

3 left

💡 Hint

Common Mistakes

Using a dash '-' which is not valid for ranges.

Separating cells with commas or semicolons instead of a colon.

The AVERAGE function calculates the mean of numbers in the range C1 to C7. The correct range uses a colon ':' like C1:C7.

4fill in blank

hard

Fill both blanks to create a formula that sums values in D1 to D5 only if they are greater than 10.

Google Sheets

=SUMIF([1], [2])

Drag options to blanks, or click blank then click option'

AD1:D5

B">10"

C<10

DD1-D5

Attempts:

3 left

💡 Hint

Common Mistakes

Using a dash '-' instead of a colon ':' for the range.

Writing the condition without quotes or with wrong comparison.

The SUMIF function sums cells in the range D1:D5 that meet the condition '>10'. The range uses a colon ':' and the condition is a string with the comparison.

5fill in blank

hard

Fill both blanks to create a formula that counts numbers in E1 to E10 that are less than 5.

Google Sheets

=COUNTIF([1], [2])

Drag options to blanks, or click blank then click option'

AE1:E10

B"<5"

CE1-E10

DE1;E10

Attempts:

3 left

💡 Hint

Common Mistakes

Using a dash '-' or semicolon ';' instead of a colon ':' for the range.

Not putting the condition in quotes.

The COUNTIF function counts cells in the range E1:E10 that satisfy the condition '<5'. The range uses a colon ':', and the condition is a string.

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