ASSIGNMENT – 32

Use of Formulas – SUMIF & SUMIFS

Topic: Sales Data Analysis Using SUMIF & SUMIFS

Sales Data

Enter the following data in Excel:

OWNER PRODUCT CLASS QUANTITY SOLD
Amit A1 4,250
Neha A4 3,180
Amit C3 8,750
Rahul C14 5,420
Neha A12 7,650
Amit B3 6,890
Rahul B7 4,275
Neha B11 3,950
Rahul A5 6,125
Amit C8 5,340
Neha C6 4,680
Rahul A9 7,210

PART A – SUMIF: Single Criterion

Q.1 Find the Total Quantity Sold by Amit.

Use: SUMIF

Q.2 Find the Total Quantity Sold by Neha.

Use: SUMIF

Q.3 Find the Total Quantity Sold by Rahul.

Use: SUMIF

Q.4 Find the Total Quantity Sold for Product Class A1.

Use: SUMIF

Q.5 Find the Total Quantity Sold for Product Class C3.

Use: SUMIF

PART B – SUMIFS: Multiple Criteria

Q.6 Find the Total Quantity Sold by Amit for Product Class C3.

Use: SUMIFS

Q.7 Find the Total Quantity Sold by Neha for Product Class A12.

Use: SUMIFS

Q.8 Find the Total Quantity Sold by Rahul for Product Class B7.

Use: SUMIFS

PART C – SUMIFS with Wildcard

Q.9 Find the Total Quantity Sold by Amit for all Product Classes starting with “C”.

Examples of Product Classes starting with C:

  • C3

  • C8

Use: SUMIFS with a wildcard.

Hint: Use C* as the criteria.

PART D – Multiple Owners

Q.10 Find the Total Quantity Sold by Neha and Rahul together.

Use: SUMIF.

Calculate:

Neha’s Total + Rahul’s Total

Formula Syntax

SUMIF

=SUMIF(criteria_range,criteria,sum_range)

Example — Amit’s Total

Assuming:

  • Owner = A2:A13

  • Product Class = B2:B13

  • Quantity Sold = C2:C13

=SUMIF(A2:A13,"Amit",C2:C13)

SUMIFS

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

Example — Amit + C3

=SUMIFS(C2:C13,A2:A13,"Amit",B2:B13,"C3")

SUMIFS with Wildcard

To find Product Classes beginning with C, use:

=SUMIFS(C2:C13,A2:A13,"Amit",B2:B13,"C*")

Here:

C* means any Product Class beginning with C.

For example:

  • C3 ✔

  • C8 ✔

  • C14 ✔

  • A1 ✘

  • B7 ✘

Q.10 Formula

You can calculate Neha and Rahul separately:

Neha

=SUMIF(A2:A13,"Neha",C2:C13)

Rahul

=SUMIF(A2:A13,"Rahul",C2:C13)

Then add both:

=SUMIF(A2:A13,"Neha",C2:C13)+SUMIF(A2:A13,"Rahul",C2:C13)

Skills Covered

Students will practice:

  • SUMIF

  • SUMIFS

  • Single-condition calculations

  • Multiple-condition calculations

  • Owner-wise sales analysis

  • Product-wise sales analysis

  • Multiple criteria

  • Wildcard *

  • Combining multiple SUMIF results

  • Practical sales data analysis

PreviousNext