SUM function in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

SUM function with mixed cell references

Given the following values in cells:

A1=5, A2=10, B1=3, B2=7

What is the result of the formula =SUM(A1:A2, B1)?

Excel

=SUM(A1:A2, B1)

A 10

B 25

C 18

D 15

💡 Hint

Remember SUM adds all numbers in the ranges and individual cells listed.

The formula sums A1 and A2 (5 + 10 = 15) plus B1 (3), total 18.

❓ Function Choice

intermediate

Choosing the correct SUM formula for non-adjacent cells

You want to add values in cells A1, C1, and E1 only. Which formula correctly sums these cells?

A =SUM(A1:C1:E1)

B =SUM(A1:C1:E1, E1)

C =SUM(A1:C1, E1)

D =SUM(A1, C1, E1)

💡 Hint

SUM can take multiple individual cells separated by commas.

Option D correctly sums the three individual cells. Option D and B use invalid range syntax. Option D sums A1 to C1 (which includes B1) plus E1, which is not what is asked.

📊 Formula Result

advanced

SUM function with dynamic range using INDIRECT

If cell A1 contains the text 'B1:B3' and cells B1=2, B2=4, B3=6, what is the result of =SUM(INDIRECT(A1))?

Excel

=SUM(INDIRECT(A1))

A 12

B 0

C Error

D 6

💡 Hint

INDIRECT converts text to a cell reference range.

INDIRECT(A1) becomes the range B1:B3. SUM adds 2 + 4 + 6 = 12.

🎯 Scenario

advanced

SUM function ignoring errors in range

Cells A1=5, A2=#DIV/0!, A3=10. Which formula will sum only the numeric values ignoring the error?

A =SUMIF(A1:A3, ">0")

B =SUM(IF(ISNUMBER(A1:A3), A1:A3))

C =SUMPRODUCT(A1:A3)

D =SUM(A1:A3)

💡 Hint

Use a formula that checks for numbers before summing.

Option B uses an array formula to sum only numbers, ignoring errors. Option B returns an error. Option B sums only positive numbers but errors cause failure. Option B returns an error due to the error cell.

❓ data_analysis

expert

Analyzing SUM with mixed data types in a range

Given the range A1:A5 contains: 3, "5", TRUE, 2, "text" (text string). What is the result of =SUM(A1:A5)?

Excel

=SUM(A1:A5)

A 6

B 5

C Error

D 7

💡 Hint

SUM ignores all text strings (even numeric-looking ones) and treats TRUE as 1.

SUM ignores text strings ("5" and "text"), treats TRUE as 1, and sums the numbers 3 and 2. Total: 3 + 1 + 2 = 6.

Practice

1. What does the SUM function do in Excel?

easy

A. Adds numbers or ranges to find a total

B. Subtracts numbers in a range

C. Finds the average of numbers

D. Counts the number of cells with numbers

Solution

1. Step 1: Understand the purpose of SUM

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

2. Step 2: Compare with other functions

   Unlike subtraction, average, or count, SUM specifically adds values.

3. Final Answer:

   Adds numbers or ranges to find a total -> Option A

4. Quick Check:

   SUM adds values [OK]

Hint: SUM always adds numbers or ranges [OK]

Common Mistakes:

• Confusing SUM with AVERAGE
• Thinking SUM counts cells
• Using SUM to subtract

2. Which of the following is the correct syntax to sum 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

   Excel uses colon (:) to specify a range from A1 to A5.

2. Step 2: Check each option

   =SUM(A1:A5) uses =SUM(A1:A5), which is correct. Others use invalid separators or operators.

3. Final Answer:

   =SUM(A1:A5) -> Option A

4. Quick Check:

   Range uses colon : [OK]

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

Common Mistakes:

• Using minus instead of colon
• Using semicolon instead of colon
• Adding cells inside SUM

3. Given the values in cells: A1=3, A2=7, A3=2, what is the result of =SUM(A1, A2, A3)?

medium

A. 0

B. 7

C. 3

D. 12

Solution

1. Step 1: Identify values in cells

   A1=3, A2=7, A3=2 are the numbers to add.

2. Step 2: Calculate the sum

   3 + 7 + 2 = 12

3. Final Answer:

   12 -> Option D

4. Quick Check:

   3+7+2=12 [OK]

Hint: Add all cell values inside SUM separated by commas [OK]

Common Mistakes:

• Adding only first two cells
• Confusing SUM with average
• Ignoring some cell values

4. The formula =SUM(A1:A3 B1:B3) gives an error. What is the fix?

medium

A. Replace colon with semicolon: =SUM(A1;A3 B1;B3)

B. Add a comma between ranges: =SUM(A1:A3, B1:B3)

C. Remove one range: =SUM(A1:A3)

D. Use plus sign between ranges: =SUM(A1:A3+B1:B3)

Solution

1. Step 1: Identify error cause

   Ranges must be separated by commas inside SUM; missing comma causes error.

2. Step 2: Correct the formula

   Insert comma between ranges: =SUM(A1:A3, B1:B3) fixes the syntax.

3. Final Answer:

   Add a comma between ranges: =SUM(A1:A3, B1:B3) -> Option B

4. Quick Check:

   Separate ranges with commas [OK]

Hint: Separate multiple ranges with commas in SUM [OK]

Common Mistakes:

• Missing commas between ranges
• Using spaces instead of commas
• Using plus sign inside SUM

5. You want to sum only the positive numbers in cells A1 to A5, where some cells have negative values. Which formula correctly sums only positive numbers?

hard

A. =SUM(A1:A5)

B. =SUMIF(A1:A5, "<0")

C. =SUMIF(A1:A5, ">0")

D. =SUM(A1:A5>0)

Solution

1. Step 1: Understand the requirement

   We want to add only positive numbers, ignoring negatives.

2. Step 2: Choose correct function

   SUMIF with condition ">0" sums only cells greater than zero.

3. Step 3: Check other options

   =SUM(A1:A5) sums all numbers, including negatives; C is invalid syntax; B sums negatives only.

4. Final Answer:

   =SUMIF(A1:A5, ">0") -> Option C

5. Quick Check:

   SUMIF with condition ">0" sums positives [OK]

Hint: Use SUMIF with ">0" to sum positives only [OK]

Common Mistakes:

• Using SUM to add all values
• Wrong condition in SUMIF
• Trying invalid syntax inside SUM