ASSIGNMENT – 13
Use of Formulas – COUNTIF, COUNTIFS & SUMIFS
Sales Data
Enter the following data into Excel and use it to solve the questions.
| SEASON | YEAR | TYPE | STATE | SALES ($) |
|---|---|---|---|---|
| Summer | 2024 | Amber Ale | California | $52,450 |
| Summer | 2024 | Hefeweizen | California | $48,750 |
| Summer | 2024 | Pale Ale | California | $57,320 |
| Summer | 2024 | Pilsner | California | $46,850 |
| Summer | 2024 | Porter | California | $43,900 |
| Summer | 2024 | Stout | California | $51,275 |
| Summer | 2024 | Amber Ale | Oregon | $45,600 |
| Summer | 2024 | Hefeweizen | Oregon | $39,850 |
| Summer | 2024 | Pale Ale | Oregon | $42,700 |
| Summer | 2024 | Pilsner | Oregon | $37,950 |
| Summer | 2024 | Porter | Oregon | $41,300 |
| Summer | 2024 | Stout | Oregon | $44,250 |
| Summer | 2024 | Amber Ale | Washington | $50,800 |
| Summer | 2024 | Hefeweizen | Washington | $47,650 |
| Summer | 2024 | Pale Ale | Washington | $53,400 |
| Summer | 2024 | Pilsner | Washington | $49,750 |
| Summer | 2024 | Porter | Washington | $38,600 |
| Summer | 2024 | Stout | Washington | $46,900 |
| Winter | 2024 | Amber Ale | California | $55,200 |
| Winter | 2024 | Hefeweizen | California | $44,800 |
| Winter | 2024 | Pale Ale | California | $59,350 |
| Winter | 2024 | Pilsner | California | $47,600 |
| Winter | 2024 | Porter | California | $42,750 |
| Winter | 2024 | Stout | California | $50,900 |
| Winter | 2024 | Amber Ale | Oregon | $36,850 |
| Winter | 2024 | Hefeweizen | Oregon | $41,250 |
| Winter | 2024 | Pale Ale | Oregon | $45,700 |
| Winter | 2024 | Pilsner | Oregon | $39,500 |
| Winter | 2024 | Porter | Oregon | $43,600 |
| Winter | 2024 | Stout | Oregon | $40,950 |
Questions
Q.1
How many records are there for Summer and Winter seasons?
Use of: COUNTIF
Find the count separately for:
-
Summer
-
Winter
Q.2
How many Summer records are from California and Oregon?
Use of: COUNTIFS
Find the count separately for:
-
Summer + California
-
Summer + Oregon
Q.3
Calculate the total sales of the Summer season in Washington.
Use of: SUMIFS
Q.4
How many Winter records are from California?
Use of: COUNTIFS
Q.5
Calculate the total sales of the Winter season in Oregon.
Use of: SUMIFS
Q.6
How many records have Sales greater than $50,000?
Use of: COUNTIF
Q.7
Calculate the total sales of Amber Ale in California for both seasons.
Use of: SUMIFS
Q.8
How many Pale Ale records are available in California and Oregon?
Use of: COUNTIFS
Find the count separately for:
-
Pale Ale + California
-
Pale Ale + Oregon
Q.9
Calculate the total sales of Stout in Washington during Summer.
Use of: SUMIFS
Q.10
Calculate the total sales of all Summer records where the State is California.
Use of: SUMIFS
Functions to Practise
| Function | Purpose |
|---|---|
COUNTIF |
Counts cells based on one condition |
COUNTIFS |
Counts records based on multiple conditions |
SUMIFS |
Adds values based on multiple conditions |
Basic Syntax
COUNTIF
=COUNTIF(range,criteria)COUNTIFS
=COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2)SUMIFS
=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2)Excel Column Structure
| Column | Field |
|---|---|
| A | SEASON |
| B | YEAR |
| C | TYPE |
| D | STATE |
| E | SALES |
Data Range: A2:E31