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.