ASSIGNMENT – 43

USE OF SUMIF & SUMIFS – SALES ANALYSIS

Objective

In this assignment, you will learn how to calculate totals based on one or multiple conditions using:

  • SUMIF

  • SUMIFS

  • Single Criteria

  • Multiple Criteria

  • Comparison Operators

  • Text Criteria

  • Number Criteria

SALES DATA

Sale ID Salesman City Product Sales Target Status
101 Aarav Sirsa Laptop 45000 50000 Pending
102 Meera Hisar Printer 28000 25000 Achieved
103 Rahul Sirsa Laptop 52000 50000 Achieved
104 Pooja Delhi Monitor 35000 30000 Achieved
105 Karan Hisar Laptop 40000 45000 Pending
106 Simran Sirsa Printer 22000 20000 Achieved
107 Mohit Delhi Laptop 60000 55000 Achieved
108 Neha Hisar Monitor 32000 30000 Achieved
109 Arjun Sirsa Monitor 27000 30000 Pending
110 Priya Delhi Printer 25000 22000 Achieved
111 Rohan Sirsa Laptop 48000 45000 Achieved
112 Ananya Hisar Printer 30000 28000 Achieved
113 Vikas Delhi Monitor 42000 40000 Achieved
114 Riya Sirsa Printer 26000 25000 Achieved
115 Nitin Hisar Laptop 37000 40000 Pending

QUESTIONS

Q1. Calculate Total Sales

Find the total sales of all salesmen.

Function: SUM

Q2. Calculate Total Sales from Sirsa

Find the total sales made in Sirsa.

Function: SUMIF

Q3. Calculate Total Sales from Hisar

Find the total sales made in Hisar.

Function: SUMIF

Q4. Calculate Total Sales of Laptop

Find the total sales amount for the Laptop product.

Function: SUMIF

Q5. Calculate Total Sales of Printer

Find the total sales amount for the Printer product.

Function: SUMIF

Q6. Calculate Achieved Sales

Find the total sales where the Status is Achieved.

Function: SUMIF

Q7. Calculate Pending Sales

Find the total sales where the Status is Pending.

Function: SUMIF

Q8. Calculate Sirsa Laptop Sales

Find the total sales where:

  • City = Sirsa

  • Product = Laptop

Function: SUMIFS

Q9. Calculate Hisar Sales Above ₹30,000

Find the total sales where:

  • City = Hisar

  • Sales > ₹30,000

Function: SUMIFS

Q10. Calculate Achieved Laptop Sales

Find the total sales where:

  • Product = Laptop

  • Status = Achieved

Function: SUMIFS

BONUS CHALLENGE ⭐

Q11.

Calculate total sales from Delhi.

Q12.

Calculate total sales where Sales ≥ ₹40,000.

Q13.

Calculate total sales from Sirsa where Status = Achieved.

Q14.

Calculate total Printer sales from Hisar.

Q15.

Calculate total sales where:

  • City = Delhi

  • Status = Achieved

  • Sales ≥ ₹40,000

FORMULA PRACTICE

SUMIF Syntax

=SUMIF(criteria_range,criteria,sum_range)

Example:

=SUMIF(C2:C16,"Sirsa",E2:E16)

SUMIFS Syntax

=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2)

Example:

=SUMIFS(E2:E16,C2:C16,"Sirsa",D2:D16,"Laptop")

IMPORTANT DIFFERENCE

SUMIF

Used when there is ONE condition.

Example:

=SUMIF(D2:D16,"Laptop",E2:E16)

SUMIFS

Used when there are TWO OR MORE conditions.

Example:

=SUMIFS(E2:E16,C2:C16,"Sirsa",D2:D16,"Laptop")

STUDENT TASK

Complete all 15 questions in Excel.

For every answer:

  1. Use the correct formula.

  2. Check the criteria carefully.

  3. Verify the calculated total.

  4. Do not manually type the final answer.

  5. Use cell references wherever possible.

PreviousNext