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.

Practice

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

easy

A. Adds all numbers in the given cells or ranges.

B. Counts how many numbers are in the cells.

C. Finds the average of the numbers in the cells.

D. Multiplies all numbers in the given cells.

Solution

1. Step 1: Understand the purpose of SUM

   The SUM function is designed to add numbers together from cells or ranges.

2. Step 2: Compare with other functions

   Counting, averaging, or multiplying are done by other functions like COUNT, AVERAGE, or PRODUCT.

3. Final Answer:

   Adds all numbers in the given cells or ranges. -> Option A

4. Quick Check:

   SUM adds numbers = Adds all numbers [OK]

Hint: SUM always adds numbers, not counts or averages [OK]

Common Mistakes:

• Thinking SUM counts cells instead of adding
• Confusing SUM with AVERAGE
• Assuming SUM multiplies numbers

2. Which of these is the correct way to write a SUM formula to add cells A1 to A5?

easy

A. =SUM(A1:A5)

B. =SUM(A1-A5)

C. =SUM(A1,A5)

D. =SUM(A1+A5)

Solution

1. Step 1: Identify correct range syntax

   In Google Sheets, a range is written with a colon, like A1:A5, to include all cells from A1 to A5.

2. Step 2: Check each option

   =SUM(A1:A5) uses colon correctly. =SUM(A1-A5) uses a minus sign which is invalid for ranges. =SUM(A1,A5) sums only two cells, not the range. =SUM(A1+A5) adds two cells directly, not a range.

3. Final Answer:

   =SUM(A1:A5) -> Option A

4. Quick Check:

   Range uses colon = =SUM(A1:A5) [OK]

Hint: Use colon (:) to specify ranges in SUM [OK]

Common Mistakes:

• Using minus (-) instead of colon (:) for ranges
• Listing cells with comma when range is needed
• Adding cells with plus (+) inside SUM

3. Given the following values in cells:
A1=3, A2=5, A3=empty, A4=7, A5="text"
What is the result of =SUM(A1:A5)?

medium

A. 0

B. 15

C. text

D. Error

Solution

1. Step 1: Identify numeric values in range

   Cells A1=3, A2=5, A4=7 are numbers. A3 is empty and A5 contains text.

2. Step 2: Understand SUM behavior with empty and text

   SUM ignores empty cells and text, so only 3 + 5 + 7 = 15 is added.

3. Final Answer:

   15 -> Option B

4. Quick Check:

   SUM ignores text and empty = 15 [OK]

Hint: SUM skips empty cells and text automatically [OK]

Common Mistakes:

• Including text as zero or error
• Counting empty cells as zero explicitly
• Expecting error due to text in range

4. You wrote the formula =SUM(A1;A2;A3) but it shows an error. What is the problem?

medium

A. The formula needs an equal sign (=) at the end.

B. SUM cannot take more than two arguments.

C. Cells A1, A2, A3 must be numbers for SUM to work.

D. Semicolons (;) are not valid separators in Google Sheets formulas.

Solution

1. Step 1: Check argument separators in formulas

   Google Sheets uses commas (,) to separate arguments, not semicolons (;).

2. Step 2: Validate formula syntax

   Using semicolons causes a syntax error. The correct formula is =SUM(A1,A2,A3).

3. Final Answer:

   Semicolons (;) are not valid separators in Google Sheets formulas. -> Option D

4. Quick Check:

   Use commas, not semicolons, in formulas [OK]

Hint: Use commas (,) to separate arguments in SUM [OK]

Common Mistakes:

• Using semicolons instead of commas
• Thinking SUM limits number of arguments
• Forgetting equal sign at start

5. You have sales data in cells B2:B10 and expenses in C2:C10. You want to find the net total (sales minus expenses). Which formula correctly uses SUM to do this?

hard

A. =SUM(B2:B10, -C2:C10)

B. =SUM(B2:B10 - C2:C10)

C. =SUM(B2:B10) - SUM(C2:C10)

D. =SUM(B2:B10 + C2:C10)

Solution

1. Step 1: Understand separate sums for sales and expenses

   We must sum sales and expenses separately to get total sales and total expenses.

2. Step 2: Subtract total expenses from total sales

   Subtract the sum of expenses from the sum of sales: =SUM(B2:B10) - SUM(C2:C10).

3. Step 3: Check incorrect options

   =SUM(B2:B10 - C2:C10) tries to subtract ranges inside SUM, which is invalid. =SUM(B2:B10, -C2:C10) uses a negative range inside SUM, which is invalid. =SUM(B2:B10 + C2:C10) adds sales and expenses, not subtracts.

4. Final Answer:

   =SUM(B2:B10) - SUM(C2:C10) -> Option C

5. Quick Check:

   Sum separately then subtract = correct net total [OK]

Hint: Sum ranges separately, then subtract for net total [OK]

Common Mistakes:

• Trying to subtract ranges inside one SUM
• Using plus instead of minus for net
• Passing negative ranges inside SUM