ASSIGNMENT – 32 — ANSWER KEY

Use of Formulas – SUMIF & SUMIFS

Data Range

  • Owner: A2:A13

  • Product Class: B2:B13

  • Quantity Sold: C2:C13

PART A – SUMIF

Q.1 — Total Quantity Sold by Amit

Amit’s sales:

  • A1 = 4,250

  • C3 = 8,750

  • B3 = 6,890

  • C8 = 5,340

Formula

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

Calculation

4,250 + 8,750 + 6,890 + 5,340

Answer:

25,230

Q.2 — Total Quantity Sold by Neha

Neha’s sales:

  • A4 = 3,180

  • A12 = 7,650

  • B11 = 3,950

  • C6 = 4,680

Formula

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

Calculation

3,180 + 7,650 + 3,950 + 4,680

Answer:

19,460

Q.3 — Total Quantity Sold by Rahul

Rahul’s sales:

  • C14 = 5,420

  • B7 = 4,275

  • A5 = 6,125

  • A9 = 7,210

Formula

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

Calculation

5,420 + 4,275 + 6,125 + 7,210

Answer:

23,030

Q.4 — Total Quantity Sold for Product Class A1

A1 appears once:

Product Class Quantity
A1 4,250

Formula

=SUMIF(B2:B13,"A1",C2:C13)

Answer:

4,250

Q.5 — Total Quantity Sold for Product Class C3

C3 appears once:

Product Class Quantity
C3 8,750

Formula

=SUMIF(B2:B13,"C3",C2:C13)

Answer:

8,750

PART B – SUMIFS

Q.6 — Amit + Product Class C3

There is one matching record:

Owner Product Class Quantity
Amit C3 8,750

Formula

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

Answer:

8,750

Q.7 — Neha + Product Class A12

Matching record:

Owner Product Class Quantity
Neha A12 7,650

Formula

=SUMIFS(C2:C13,A2:A13,"Neha",B2:B13,"A12")

Answer:

7,650

Q.8 — Rahul + Product Class B7

Matching record:

Owner Product Class Quantity
Rahul B7 4,275

Formula

=SUMIFS(C2:C13,A2:A13,"Rahul",B2:B13,"B7")

Answer:

4,275

Q.9 — Amit + Product Classes Starting with “C”

Amit has two Product Classes beginning with C:

Owner Product Class Quantity
Amit C3 8,750
Amit C8 5,340

Formula

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

Calculation

8,750 + 5,340 = 14,090

Answer:

14,090

Q.10 — Neha and Rahul Together

Neha Total

19,460

Rahul Total

23,030

Calculation

19,460 + 23,030 = 42,490

Formula

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

Answer:

42,490

Final Answer Summary

Q. Question Answer
Q.1 Total Quantity – Amit 25,230
Q.2 Total Quantity – Neha 19,460
Q.3 Total Quantity – Rahul 23,030
Q.4 Product Class A1 4,250
Q.5 Product Class C3 8,750
Q.6 Amit + C3 8,750
Q.7 Neha + A12 7,650
Q.8 Rahul + B7 4,275
Q.9 Amit + Product Classes starting with C 14,090
Q.10 Neha + Rahul 42,490

⭐ Important Concept

SUMIF = One criterion

=SUMIF(range,criteria,sum_range)

SUMIFS = Multiple criteria

=SUMIFS(sum_range,range1,criteria1,range2,criteria2)

Wildcard * = Any characters after the given text

"C*"

इसका मतलब है: C से शुरू होने वाला कोई भी Product Class.

PreviousNext