Introduction
The SUM function in Excel helps you add numbers and calculate totals quickly. It is widely used in sales reports, employee salary sheets, expense tracking, inventory management, and financial reporting.
In this tutorial, you will learn the SUM function through practical company-based examples and hands-on challenges. Start with basic formulas and gradually move to advanced calculations involving multiple worksheets, Excel Tables, and business reports.
Your goal: Understand how to use the SUM function in Excel Tutorial confidently in real-world Excel tasks.
SUM Function Syntax
=SUM(number1, [number2], ...)number1: The first number, cell reference, or range to add.number2: Additional numbers, cells, or ranges (optional).
Example:
=SUM(B2:B6)This formula adds all numeric values in cells B2 through B6.
Question 1: Calculate Total Sales for Five Days
Problem Statement
A company wants to calculate its total sales for five working days.
| Day | Sales |
|---|---|
| Monday | 12,000 |
| Tuesday | 15,000 |
| Wednesday | 18,000 |
| Thursday | 20,000 |
| Friday | 25,000 |
Assume the sales figures are in cells B2.
Excel Formula
=SUM(B2:B6)Expected Result: ₹90,000
Student Task: Add Saturday sales of ₹10,000 in cell B7. Update the formula to include Saturday.
New Expected Result: ₹1,00,000
Question 2: Add Only Selected Cells
Problem Statement
A sales manager wants to calculate the total sales for Monday, Wednesday, and Friday only.
| Cell | Day | Sales |
|---|---|---|
| C2 | Monday | 10,000 |
| C3 | Tuesday | 15,000 |
| C4 | Wednesday | 12,000 |
| C5 | Thursday | 18,000 |
| C6 | Friday | 20,000 |
Excel Formula
=SUM(C2,C4,C6)Expected Result: ₹42,000
Student Task: Calculate the total sales for Tuesday and Thursday only. Write the formula yourself before checking your answer.
Answer: =SUM(C3,C5) — ₹33,000
Question 3: Add Values from Separate Ranges
Problem Statement
An office wants to calculate its total travel and food expenses.
| Travel Expense | Amount |
|---|---|
| Taxi | 500 |
| Train | 800 |
| Bus | 300 |
Assume these amounts are in B2.
| Food Expense | Amount |
|---|---|
| Breakfast | 200 |
| Lunch | 350 |
| Dinner | 450 |
Assume these amounts are in D2.
Excel Formula
=SUM(B2:B4,D2:D4)Expected Result: ₹2,600
Student Task: Add a new expense of ₹600 in cell D5. Update the formula to include it.
New Expected Result: ₹3,200
Question 4: Calculate an Employee’s Gross Salary
Problem Statement
An HR department wants to calculate an employee’s gross salary using the following components.
| Salary Component | Amount |
|---|---|
| Basic Salary | 30,000 |
| HRA | 8,000 |
| DA | 4,000 |
| TA | 2,000 |
Assume the amounts are in B2.
Excel Formula
=SUM(B2:B5)Expected Result: ₹44,000
Student Task: Add a medical allowance of ₹1,500 in B6 and update the formula.
New Expected Result: ₹45,500
Remember that gross salary is calculated before deductions such as PF and income tax.
Question 5: Calculate Total Revenue from a Sales Report
Problem Statement
A company tracks monthly sales for three branches.
| Branch | January | February | March |
|---|---|---|---|
| Delhi | 1,00,000 | 1,20,000 | 1,30,000 |
| Mumbai | 90,000 | 1,10,000 | 1,25,000 |
| Jaipur | 70,000 | 80,000 | 95,000 |
Assume the sales data is in B2.
Excel Formula
=SUM(B2:D4)Expected Result: ₹9,20,000
Student Task: Calculate Delhi’s total sales for all three months using a separate SUM formula.
Answer: =SUM(B2:D2) — ₹3,50,000
Question 6: Calculate Sales After a Percentage Increase
Problem Statement
A company recorded the following sales and expects an overall increase of 10%.
| Month | Original Sales |
|---|---|
| January | 50,000 |
| February | 60,000 |
| March | 70,000 |
Assume the sales figures are in B2.
Excel Formula
=SUM(B2:B4)*110%Expected Result: ₹1,98,000
Student Task: Calculate the total sales after a 15% increase instead. Change the formula yourself.
Answer: =SUM(B2:B4)*115% — ₹2,07,000
Question 7: Calculate Total Revenue Using a Helper Column
Problem Statement
A company wants to calculate revenue by multiplying each product’s quantity by its unit price.
| Product | Quantity | Unit Price | Revenue |
|---|---|---|---|
| Keyboard | 10 | 800 | |
| Mouse | 15 | 500 | |
| Monitor | 5 | 8,000 |
Assume Quantity is in column B, Unit Price in column C, and Revenue in column D.
Excel Formulas
Enter this formula in D2:
=B2*C2Copy the formula down to D4.
Then calculate total revenue:
=SUM(D2:D4)Expected Result: ₹55,500
Student Task: Add a printer with a quantity of 4 and a unit price of ₹5,000. Calculate the updated total revenue.
New Expected Result: ₹75,500
Question 8: Calculate Total Sales Across Multiple Worksheets
Problem Statement
A company maintains separate worksheets named January and February.
January Worksheet
| Cell | Sales |
|---|---|
| B2 | 20,000 |
| B3 | 25,000 |
| B4 | 30,000 |
February Worksheet
| Cell | Sales |
|---|---|
| B2 | 35,000 |
| B3 | 28,000 |
| B4 | 32,000 |
Excel Formula
=SUM(January!B2:B4,February!B2:B4)Expected Result: ₹1,70,000
Student Task: Add March sales of ₹40,000, ₹45,000, and ₹50,000 on a worksheet named March. Update the formula to include all three months.
New Expected Result: ₹3,05,000
Question 9: Consolidate Monthly Reports Using a 3-D Reference
Problem Statement
A company maintains three worksheets named Jan, Feb, and Mar. Each worksheet stores sales values in B2.
| Worksheet | Sales Values | Monthly Total |
|---|---|---|
| Jan | 10,000; 15,000; 20,000; 25,000 | 70,000 |
| Feb | 12,000; 18,000; 22,000; 28,000 | 80,000 |
| Mar | 14,000; 16,000; 24,000; 26,000 | 80,000 |
Excel Formula
=SUM(Jan:Mar!B2:B5)Expected Result: ₹2,30,000
Student Task: Add a new worksheet named Apr after Mar and enter sales values of ₹20,000, ₹25,000, ₹30,000, and ₹35,000 in B2. Extend the 3-D reference to include April.
New Expected Result: ₹3,40,000
Important: The 3-D reference includes all worksheets between its starting and ending sheets, inclusive. Keep the monthly worksheets in the intended order.
Question 10: Calculate Total Sales Using an Excel Table
Problem Statement
A company stores its sales records in an Excel Table named SalesTable.
| Product | Sales |
|---|---|
| Keyboard | 8,000 |
| Mouse | 5,000 |
| Monitor | 25,000 |
| Printer | 12,000 |
The sales column is named Sales.
Excel Formula
=SUM(SalesTable[Sales])Expected Result: ₹50,000
Student Task: Add a new product called Webcam with sales of ₹6,000 to the Table. Check whether the formula updates automatically.
New Expected Result: ₹56,000
Note: The table and column names in your formula must match your actual Excel Table.
Question 11: Calculate Total Using a Named Range
Problem Statement
A company records monthly sales in cells B2 and defines the named range MonthlySales for that range.
| Month | Sales |
|---|---|
| January | 15,000 |
| February | 20,000 |
| March | 25,000 |
| April | 30,000 |
| May | 35,000 |
Excel Formula
=SUM(MonthlySales)Expected Result: ₹1,25,000
Student Task: Extend the named range to include June sales of ₹40,000 in B7. Calculate the updated total.
New Expected Result: ₹1,65,000
Note: The named range must be updated before the new value is included.
Question 12: Understand SUM with Filtered Data
Problem Statement
A company tracks sales by region.
| Employee | Region | Sales |
|---|---|---|
| Amit | North | 20,000 |
| Priya | South | 15,000 |
| Rahul | North | 25,000 |
| Neha | South | 18,000 |
| Karan | North | 22,000 |
Assume sales are in C2.
Excel Formula
=SUM(C2:C6)Expected Result Before Filtering: ₹1,00,000
Student Task: Filter the Region column to show North only. Check whether the SUM formula changes.
Result: The SUM formula remains ₹1,00,000 because it includes filtered-out rows.
If you need the total of visible rows only, use:
=SUBTOTAL(9,C2:C6)Expected Visible Total for North: ₹67,000
This challenge helps you understand when SUM is appropriate and when SUBTOTAL is a better choice.
Final Student Challenge: Build a Sales Summary
Now put your learning into practice with a small business task.
Problem Statement
A company records sales for four products. Create a worksheet and calculate the total sales amount.
| Product | Quantity | Price per Unit | Total Sales |
|---|---|---|---|
| Keyboard | 12 | 800 | |
| Mouse | 20 | 500 | |
| Monitor | 6 | 8,000 | |
| Printer | 5 | 5,000 | |
| Webcam | 10 | 1,200 |
Your Tasks
- Calculate the sales amount for each product by multiplying quantity by price.
- Calculate the combined sales using the SUM function.
- Add a new product with a quantity of 8 and a unit price of ₹2,000.
- Update your total to include the new product.
- Convert your data into an Excel Table and try a structured reference.
Check Your Answers
Initial Total Sales: ₹1,10,000
Additional Product Sales: ₹16,000
Updated Total Sales: ₹1,26,000
Challenge: Can you complete all five tasks without looking at the formulas in the earlier questions?
Key Takeaways
- Use SUM to add numbers and cell ranges.
- Use separate cell references when only specific cells should be included.
- Combine multiple ranges to calculate totals from different parts of a worksheet.
- Apply SUM to salaries, revenue, expenses, and business reports.
- Use worksheet references and 3-D references to consolidate monthly data.
- Use Excel Tables and named ranges to make formulas easier to maintain.
- Combine SUM with multiplication and percentages to solve business problems.
- Remember that SUM includes filtered-out rows; SUBTOTAL is better for visible-only totals.
Frequently Asked Questions (FAQs)
1. What is the SUM function in Excel?
The SUM function adds numeric values from cells, ranges, or numbers entered directly into a formula.
2. Can SUM add values from different worksheets?
Yes. You can reference multiple worksheets in one formula, provided the worksheet names and cell references are correct.
3. What is the difference between SUM and SUBTOTAL?
SUM includes numeric values from filtered-out rows, whereas SUBTOTAL can calculate a total based on visible rows.
4. Does SUM ignore blank cells?
Yes. SUM ignores blank cells in referenced ranges.
5. Can SUM work with Excel Tables?
Yes. A formula such as =SUM(SalesTable[Sales]) calculates the total of a Table column and automatically accommodates new Table rows.
6. What is a 3-D reference in Excel?
A 3-D reference lets you calculate totals across the same range on multiple worksheets, such as monthly reports.
7. Can SUM calculate totals after a percentage increase?
Yes. For example, =SUM(B2:B10)*110% calculates the total after a 10% increase.
8. Why does SUM not include a new row?
A fixed-range formula does not include rows outside its specified range. Excel Tables with structured references can help address this problem.