Value aggregation (SUM, COUNT, AVG) in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate the average excluding zeros

You have numbers in cells A1 to A5: 10, 0, 20, 0, 30.
Which formula correctly calculates the average of only the non-zero numbers?

A =AVERAGEIF(A1:A5, "<>0")

B =SUM(A1:A5)/COUNTIF(A1:A5, "<>0")

C =SUM(A1:A5)/COUNT(A1:A5)

D =AVERAGE(A1:A5)

💡 Hint

Use a function that averages only cells meeting a condition.

AVERAGEIF calculates the average of cells that meet the condition "not equal to zero". Option A correctly excludes zeros from the average.

📊 Formula Result

intermediate

Count only numeric values

Cells B1 to B6 contain: 5, "text", 10, "", 15, TRUE.
Which formula counts only the numeric values?

A =COUNTIF(B1:B6, ">0")

B =COUNTA(B1:B6)

C =COUNT(B1:B6)

D =COUNTBLANK(B1:B6)

💡 Hint

Count only numbers, ignoring text and blanks.

COUNT counts only numeric values. Here, it counts 5, 10, and 15, ignoring text, blanks, and TRUE.

❓ Function Choice

advanced

Sum values ignoring errors

You have numbers in C1:C5 but some cells contain errors (#DIV/0!).
Which formula sums only the valid numbers, ignoring errors?

A =SUMPRODUCT(C1:C5)

B =SUM(C1:C5)

C =SUMIF(C1:C5, ">0")

D =SUM(IFERROR(C1:C5, 0))

💡 Hint

Replace errors with zero before summing.

IFERROR replaces errors with 0, so SUM sums only valid numbers. Option D correctly handles errors.

❓ data_analysis

advanced

Average with multiple conditions

In columns D and E, you have sales data and regions.
Which formula calculates the average sales in region "West" where sales are above 100?

A =AVERAGEIF(E1:E10, "West", D1:D10)

B =AVERAGEIFS(D1:D10, E1:E10, "West", D1:D10, ">100")

C =AVERAGEIF(D1:D10, ">100")

D =SUMIFS(D1:D10, E1:E10, "West", D1:D10, ">100")/COUNTIFS(E1:E10, "West", D1:D10, ">100")

💡 Hint

Use a function that supports multiple conditions for averaging.

AVERAGEIFS calculates average with multiple criteria. Option B correctly averages sales >100 in region West.

🎯 Scenario

expert

Dynamic count excluding blanks and errors

You want to count how many cells in F1:F20 contain valid numbers, excluding blanks and errors.
Which formula achieves this correctly?

A =SUMPRODUCT(ISNUMBER(F1:F20)*NOT(ISERROR(F1:F20)))

B =COUNTA(F1:F20)-COUNTIF(F1:F20, "#N/A")

C =COUNTIF(F1:F20, "<>"&"")

D =COUNT(F1:F20)

💡 Hint

Combine checks for numbers and errors using array formulas.

SUMPRODUCT with ISNUMBER and NOT(ISERROR) counts only valid numbers, excluding blanks and errors. Option A is correct.

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