---

BEGINNER LEVEL (1–15)

---

✅ Task 1: Total Shop Sales

Dataset:

Item	Sales
Pen	120
Pencil	80
Notebook	200

Description: Calculate total sales of all items.

Solution Formula:
=SUM(B2:B4)

Steps:

1. Select cell B6
2. Type =SUM(B2:B4)
3. Press Enter

---

✅ Task 2: Student Total Marks

Dataset:

Subject	Marks
Math	75
Science	68
English	80

Description: Find total marks.

Formula: =SUM(B2:B4)

Steps: Same as Task 1

---

✅ Task 3: Weekly Income

Dataset:

Day	Income
Mon	1000
Tue	1200
Wed	900
Thu	1100

Description: Calculate total weekly income.

Formula: =SUM(B2:B5)

---

✅ Task 4: Two Number Addition

Dataset:

A	B
25	35

Description: Add two numbers.

Formula: =SUM(A2,B2)

---

✅ Task 5: Grocery Bill

Dataset:

Item	Price
Rice	500
Oil	150
Sugar	200

Description: Find total bill.

Formula: =SUM(B2:B4)

---

✅ Task 6: Daily Steps

Dataset:

Day	Steps
Mon	4000
Tue	5000
Wed	6000

Description: Calculate total steps.

Formula: =SUM(B2:B4)

---

✅ Task 7: Employee Salary Total

Dataset:

Employee	Salary
A	20000
B	25000
C	30000

Description: Total salary payout.

Formula: =SUM(B2:B4)

---

✅ Task 8: Monthly Expenses

Dataset:

Month	Expense
Jan	5000
Feb	6000
Mar	7000

Formula: =SUM(B2:B4)

---

✅ Task 9: Non-Adjacent Cells

Dataset:

A	B	C
10	20	30

Description: Add A and C only.

Formula: =SUM(A2,C2)

---

✅ Task 10: Electricity Units

Dataset:

Day	Units
Mon	5
Tue	7
Wed	6

Formula: =SUM(B2:B4)

---

✅ Task 11: Water Intake

Day	Liters
Mon	2
Tue	3
Wed	2

Formula: =SUM(B2:B4)

---

✅ Task 12: Mobile Recharge

Recharge	Amount
Jio	299
Airtel	399

Formula: =SUM(B2:B3)

---

✅ Task 13: Petrol Cost

Day	Cost
Mon	500
Tue	600

Formula: =SUM(B2:B3)

---

✅ Task 14: Book Prices

Book	Price
A	150
B	200
C	250

Formula: =SUM(B2:B4)

---

✅ Task 15: Fruit Quantity

Fruit	Qty
Apple	5
Mango	7
Banana	10

Formula: =SUM(B2:B4)

---

INTERMEDIATE LEVEL (16–35)

---

✅ Task 16: SUMIF (Single Condition)

Dataset:

Product	Sales
Pen	100
Pencil	50
Pen	200

Description: Sum only “Pen” sales.

Formula:
=SUMIF(A2:A4,"Pen",B2:B4)

Steps:

1. Select result cell
2. Type formula
3. Press Enter

---

✅ Task 17: Sum Above 100

Values
50
120
200

Formula: =SUMIF(A2:A4,">100")

---

✅ Task 18: Sum Below 150

Formula: =SUMIF(A2:A4,"<150")

---

✅ Task 19: Department Salary

Dept	Salary
IT	50000
HR	30000
IT	40000

Formula: =SUMIF(A2:A4,"IT",B2:B4)

---

✅ Task 20: Multi Column SUM

Jan	Feb	Mar
100	200	300

Formula: =SUM(A2:C2)

---

✅ Task 21: Entire Column

Formula: =SUM(B:B)

---

✅ Task 22: Entire Row

Formula: =SUM(2:2)

---

✅ Task 23: Negative Values

Values
-50
100
-20

Formula: =SUM(A2:A4)

---

✅ Task 24: Named Range

Define range → “Sales”
Formula: =SUM(Sales)

---

✅ Task 25: Filtered Data

Formula: =SUBTOTAL(9,B2:B10)

---

✅ Task 26: Multiple Conditions

Region	Sales
East	100
West	200

Formula: =SUMIFS(B2:B3,A2:A3,"East")

---

✅ Task 27: Date Based SUM

Date	Sales
01-Jan	100
02-Jan	200

Formula:
=SUMIFS(B2:B3,A2:A3,">=01-Jan-2025")

---

✅ Task 28: Text Criteria

Formula: =SUMIF(A2:A4,"Pen*",B2:B4)

---

✅ Task 29: Multiple Criteria SUM

Formula:
=SUMIFS(C2:C4,A2:A4,"East",B2:B4,"Pen")

---

✅ Task 30: Visible Cells Only

Formula: =SUBTOTAL(109,A2:A10)

---

✅ Task 31–35 (Practice with same format)

• City-based sales SUM
• Product category SUM
• Monthly filter SUM
• Employee department SUM
• Project budget SUM

---

EXPERT LEVEL (36–50)

---

✅ Task 36: SUM with OR Logic

Dataset:
| Product | Sales |
| Pen | 100 |
| Pencil | 200 |
| Eraser | 50 |

Formula:
=SUM(SUMIF(A2:A4,{"Pen","Pencil"},B2:B4))

---

✅ Task 37: SUM with INDIRECT

Formula: =SUM(INDIRECT("B2:B10"))

---

✅ Task 38: SUM Across Sheets

Formula: =SUM(Sheet1:Sheet3!B2)

---

✅ Task 39: Dynamic SUM

Formula:
=SUM(OFFSET(A1,1,0,5,1))

---

✅ Task 40: FILTER + SUM

Formula:
=SUM(FILTER(B2:B10,A2:A10="Pen"))

---

✅ Task 41: Top 3 Values

Formula:
=SUM(LARGE(A2:A10,{1,2,3}))

---

✅ Task 42: Bottom Values

Formula:
=SUM(SMALL(A2:A10,{1,2,3}))

---

✅ Task 43: Exclude Zeros

Formula:
=SUMIF(A2:A10,"<>0")

---

✅ Task 44: Pivot Table SUM

Steps:

1. Insert Pivot Table
2. Drag field → Values
3. Set to SUM

---

✅ Task 45: Array SUM

Formula:
=SUM((A2:A10="Pen")*B2:B10)

---

✅ Task 46: SUMPRODUCT

Formula:
=SUMPRODUCT((A2:A10="Pen")*B2:B10)

---

✅ Task 47: Multi-Sheet Dynamic

Formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!A2:A10"),"Pen",INDIRECT("'"&Sheets&"'!B2:B10")))

---

✅ Task 48: SUM with Conditions + Dates

Formula:
=SUMIFS(B2:B10,A2:A10,">01-Jan-2025")

---

✅ Task 49: Weighted SUM

Product	Qty	Price
Pen	10	5

Formula:
=SUMPRODUCT(B2:B4,C2:C4)

---

✅ Task 50: Complex Dashboard SUM (Complete Version)

---

📊 Dataset

Date	Product	Region	Sales
01-Jan-2025	Pen	East	100
02-Jan-2025	Pencil	West	200
03-Jan-2025	Pen	East	150
04-Jan-2025	Pen	West	300
05-Jan-2025	Pencil	East	250
06-Jan-2025	Pen	East	200

---

❓ Question (Dashboard Requirement)

Create a dashboard calculation to:

👉 Total Pen sales
👉 Only for East region
👉 Only for dates after 01-Jan-2025
👉 And ensure it works correctly even when data is filtered

---

✅ Final Solution Formula

=SUMPRODUCT(
(SUBTOTAL(103,OFFSET(A2:A7,ROW(A2:A7)-ROW(A2),0,1)))*
(B2:B7="Pen")*
(C2:C7="East")*
(A2:A7>DATE(2025,1,1))*
(D2:D7)
)

---

🧠 Logic Explanation

This formula combines:

• SUBTOTAL(103,...) → counts only visible rows (ignores filtered rows)
• SUMPRODUCT → applies multiple conditions
• Conditions:
  • Product = “Pen”
  • Region = “East”
  • Date > 01-Jan-2025

---

🪜 Step-by-Step Instructions

🔹 Step 1: Enter Data

• Paste dataset into Excel (A1:D7)

---

🔹 Step 2: Apply Filter (Optional for testing)

1. Select header row
2. Go to Data → Filter
3. Try hiding some rows

---

🔹 Step 3: Enter Formula

1. Click any empty cell
2. Paste formula
3. Press Enter

---

🔹 Step 4: Verify Result

Matching rows:

• 03-Jan → 150
• 06-Jan → 200

👉 Total = 350

---

🎯 Alternative (Simpler Version without Filter Condition)

If you don’t need filter support:

=SUMIFS(D2:D7,B2:B7,"Pen",C2:C7,"East",A2:A7,">01-Jan-2025")

---

💡 When to Use Which

Scenario	Use
Simple conditions	SUMIFS
Filtered data	SUBTOTAL
Complex logic	SUMPRODUCT

---