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.