SUM function in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SUM function do in Excel?

The SUM function adds together all the numbers in a range of cells and gives you the total.

beginner

How do you write a SUM formula to add cells A1 to A5?

You write =SUM(A1:A5) to add all numbers from cell A1 through A5.

beginner

Can the SUM function add numbers that are not next to each other? How?

Yes, you can add numbers from different cells by listing them separated by commas, like =SUM(A1, C1, E1).

beginner

What happens if some cells in the SUM range are empty or contain text?

Empty cells or cells with text are ignored by SUM. Only numbers are added.

beginner

Why is using the SUM function better than typing numbers and adding with +?

SUM is faster, less error-prone, and updates automatically if you change any number in the range.

What formula adds all numbers from B2 to B10?

A =SUM(B2+B10)

B =ADD(B2:B10)

C =TOTAL(B2:B10)

D =SUM(B2:B10)

The correct syntax to add a range is =SUM(B2:B10).

Which cells will =SUM(A1, A3, A5) add?

A Cells A1, A2, A3, A4, A5

B Cells A1 to A5 except A3

C Cells A1, A3, and A5 only

D Only cell A1

Listing cells separated by commas adds only those specific cells.

If cell A2 is empty, what does =SUM(A1:A3) do?

A Ignores A2 and adds A1 and A3

B Counts A2 as zero and adds all three

C Returns an error

D Adds only A2

Empty cells count as zero, so SUM adds the other numbers plus zero.

Which is a correct way to add numbers in cells A1, B1, and C1?

A =SUM(A1, B1, C1)

B =SUM(A1:B1:C1)

C =SUM(A1-B1-C1)

D =SUM(A1 B1 C1)

Use commas to separate individual cells in SUM.

Why use SUM instead of typing =A1+A2+A3?

A SUM updates automatically if you add more cells

B SUM is slower

C Typing + is more accurate

D SUM cannot add ranges

SUM can easily include ranges and updates if you change numbers.

Explain how to use the SUM function to add numbers in a column.

Think about how to select cells from top to bottom.

Describe what happens when the SUM function encounters empty cells or text in the range.

Consider what SUM counts as numbers.

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