ASSIGNMENT – 13
Answer Key – COUNTIF, COUNTIFS & SUMIFS
Data Range
-
Season:
A2:A31 -
Year:
B2:B31 -
Type:
C2:C31 -
State:
D2:D31 -
Sales:
E2:E31
Q.1 How many records are there for Summer and Winter?
Summer
=COUNTIF(A2:A31,"Summer")Answer: 18 records
Winter
=COUNTIF(A2:A31,"Winter")Answer: 12 records
| Season | Records |
|---|---|
| Summer | 18 |
| Winter | 12 |
| Total | 30 |
Q.2 How many Summer records are from California and Oregon?
Summer + California
=COUNTIFS(A2:A31,"Summer",D2:D31,"California")Answer: 6 records
Summer + Oregon
=COUNTIFS(A2:A31,"Summer",D2:D31,"Oregon")Answer: 6 records
| Season | State | Records |
|---|---|---|
| Summer | California | 6 |
| Summer | Oregon | 6 |
Q.3 Total Sales of Summer in Washington
=SUMIFS(E2:E31,A2:A31,"Summer",D2:D31,"Washington")Answer: $287,100
Calculation
$50,800 + $47,650 + $53,400 + $49,750 + $38,600 + $46,900
= $287,100
Q.4 How many Winter records are from California?
=COUNTIFS(A2:A31,"Winter",D2:D31,"California")Answer: 6 records
Q.5 Total Sales of Winter in Oregon
=SUMIFS(E2:E31,A2:A31,"Winter",D2:D31,"Oregon")Answer: $247,850
Calculation
$36,850 + $41,250 + $45,700 + $39,500 + $43,600 + $40,950
= $247,850
Q.6 How many records have Sales greater than $50,000?
=COUNTIF(E2:E31,">50000")Answer: 8 records
Records above $50,000
| Season | Type | State | Sales |
|---|---|---|---|
| Summer | Amber Ale | California | $52,450 |
| Summer | Pale Ale | California | $57,320 |
| Summer | Stout | California | $51,275 |
| Summer | Amber Ale | Washington | $50,800 |
| Summer | Pale Ale | Washington | $53,400 |
| Winter | Amber Ale | California | $55,200 |
| Winter | Pale Ale | California | $59,350 |
| Winter | Stout | California | $50,900 |
Total = 8 records
Q.7 Total Sales of Amber Ale in California for Both Seasons
=SUMIFS(E2:E31,C2:C31,"Amber Ale",D2:D31,"California")Answer: $107,650
Calculation
Summer Amber Ale = $52,450
Winter Amber Ale = $55,200
Total = $52,450 + $55,200 = $107,650
Q.8 How many Pale Ale records are available in California and Oregon?
Pale Ale + California
=COUNTIFS(C2:C31,"Pale Ale",D2:D31,"California")Answer: 2 records
Pale Ale + Oregon
=COUNTIFS(C2:C31,"Pale Ale",D2:D31,"Oregon")Answer: 2 records
| Type | State | Records |
|---|---|---|
| Pale Ale | California | 2 |
| Pale Ale | Oregon | 2 |
| Total | 4 |
Q.9 Total Sales of Stout in Washington during Summer
=SUMIFS(E2:E31,A2:A31,"Summer",C2:C31,"Stout",D2:D31,"Washington")Answer: $46,900
Q.10 Total Sales of all Summer records where State is California
=SUMIFS(E2:E31,A2:A31,"Summer",D2:D31,"California")Answer: $300,545
Calculation
$52,450 + $48,750 + $57,320 + $46,850 + $43,900 + $51,275
= $300,545
Final Answer Summary
| Q. No. | Question | Answer |
|---|---|---|
| Q1 | Summer Records | 18 |
| Q1 | Winter Records | 12 |
| Q2 | Summer + California | 6 |
| Q2 | Summer + Oregon | 6 |
| Q3 | Summer + Washington Sales | $287,100 |
| Q4 | Winter + California Records | 6 |
| Q5 | Winter + Oregon Sales | $247,850 |
| Q6 | Sales > $50,000 | 8 |
| Q7 | Amber Ale + California Sales | $107,650 |
| Q8 | Pale Ale + California | 2 |
| Q8 | Pale Ale + Oregon | 2 |
| Q9 | Summer + Stout + Washington | $46,900 |
| Q10 | Summer + California Sales | $300,545 |
Important Difference
-
COUNTIF → one condition
-
COUNTIFS → two or more conditions
-
SUMIFS → adds values when multiple conditions are satisfied
For example:
=SUMIFS(E2:E31,A2:A31,"Summer",D2:D31,"California")This means:
Add Sales → where Season = Summer AND State = California.