SUM function in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

SUM function with mixed data types

What is the result of the formula =SUM(A1:A5) if the cells contain the following values?

A1: 10
A2: "5" (text)
A3: 15
A4: TRUE
A5: 20

A 46

B Error

C 50

D 60

💡 Hint

Remember that SUM ignores text and treats TRUE as 1.

SUM adds numbers and treats TRUE as 1, but ignores text. So it sums 10 + 15 + 1 + 20 = 46.

📊 Formula Result

intermediate

SUM function with non-contiguous ranges

What is the result of the formula =SUM(A1:A3, C1:C2) if the cells contain:

A1: 5
A2: 10
A3: 15
C1: 20
C2: 25

A 65

B 70

C 75

D Error

💡 Hint

Add all values from both ranges.

SUM adds all numbers in A1:A3 (5+10+15=30) and C1:C2 (20+25=45). Total is 30 + 45 = 75.

❓ Function Choice

advanced

Choosing the right function for conditional sum

You want to sum values in column B only if the corresponding cell in column A is "Yes". Which formula should you use?

A =SUMIF(A1:A10, "Yes", B1:B10)

B =SUM(B1:B10, A1:A10="Yes")

C =SUM(B1:B10 * (A1:A10="Yes"))

D =SUMIFS(B1:B10, A1:A10, "Yes")

💡 Hint

Look for a function that sums with a condition.

SUMIF(range, criteria, sum_range) sums values in sum_range where range meets criteria. Option A is correct syntax. Option D (SUMIFS) is also valid. Option B is invalid syntax. Option C causes an error because array multiplication is not supported directly in SUM.

📊 Formula Result

advanced

SUM function with error values in range

What happens when you use =SUM(A1:A4) if:

A1: 10
A2: #DIV/0!
A3: 20
A4: 30

A 60

B #DIV/0! error

C 40

D 30

💡 Hint

SUM stops if any cell in range has an error.

If any cell in the range has an error like #DIV/0!, SUM returns that error instead of a number.

🎯 Scenario

expert

Summing values with dynamic range using INDIRECT

You want to sum values in column B from row 1 to a row number specified in cell D1. Which formula correctly does this?

A =SUM(INDIRECT("B1:B" & D1))

B =SUM(B1:B&D1)

C =SUM(B1:INDIRECT(D1))

D =SUM(INDIRECT(B1:B & D1))

💡 Hint

Use INDIRECT to build a range from text.

INDIRECT("B1:B" & D1) creates a range from B1 to B plus the row number in D1. SUM then adds that range. Option A is invalid syntax. Option A tries to mix range and INDIRECT incorrectly. Option A has incorrect INDIRECT argument.