Value aggregation (SUM, COUNT, AVG) in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a summary report showing total sales, number of sales, and average sale amount for each product category.

📊 Data: You have a sales data table with columns: Date, Product Category, Product Name, and Sale Amount.

🎯 Deliverable: Create a summary table that shows for each Product Category: Total Sales, Number of Sales, and Average Sale Amount.

Sample Data

Date	Product Category	Product Name	Sale Amount
2024-06-01	Electronics	Headphones	120
2024-06-02	Electronics	Smartphone	800
2024-06-03	Home	Blender	60
2024-06-04	Home	Vacuum Cleaner	150
2024-06-05	Clothing	T-Shirt	25
2024-06-06	Clothing	Jeans	50
2024-06-07	Electronics	Tablet	300
2024-06-08	Home	Toaster	40
2024-06-09	Clothing	Jacket	100
2024-06-10	Electronics	Camera	450

1

Step 1: List unique product categories in a new column to prepare for summary.

In a new column, enter: =UNIQUE(B2:B11)

Expected Result

A list of unique categories: Electronics, Home, Clothing

2

Step 2: Calculate total sales for each product category using SUMIF.

Next to each category, enter: =SUMIF(B$2:B$11, E2, D$2:D$11)

Expected Result

Electronics total: 1670, Home total: 250, Clothing total: 175

3

Step 3: Count number of sales for each product category using COUNTIF.

Next to total sales, enter: =COUNTIF(B$2:B$11, E2)

Expected Result

Electronics count: 4, Home count: 3, Clothing count: 3

4

Step 4: Calculate average sale amount for each product category using AVERAGEIF.

Next to count, enter: =AVERAGEIF(B$2:B$11, E2, D$2:D$11)

Expected Result

Electronics average: 417.5, Home average: 83.33, Clothing average: 58.33

Final Result

Product Category | Total Sales | Number of Sales | Average Sale Amount
-----------------|-------------|-----------------|--------------------
Electronics      | 1670        | 4               | 417.5
Home             | 250         | 3               | 83.33
Clothing         | 175         | 3               | 58.33

✓Electronics has the highest total sales and average sale amount.

✓Home category has moderate sales with fewer transactions.

✓Clothing has the lowest total and average sales among the categories.

Bonus Challenge

Create a chart that visually compares total sales for each product category.

Show Hint

Use Insert > Chart, select the summary table, and choose a column chart for clear comparison.

Practice

1. What does the SUM function do in Google Sheets?

easy

A. Adds all numbers in a selected range

B. Counts how many cells have numbers

C. Finds the highest number in a range

D. Calculates the average of numbers

Solution

1. Step 1: Understand the purpose of SUM

   The SUM function adds all numeric values in the specified range.

2. Step 2: Compare with other functions

   COUNT counts numbers, AVERAGE calculates mean, MAX finds highest number.

3. Final Answer:

   Adds all numbers in a selected range -> Option A

4. Quick Check:

   SUM = addition [OK]

Hint: SUM adds numbers; think of adding bills [OK]

Common Mistakes:

• Confusing SUM with COUNT
• Thinking SUM finds average
• Using SUM for text values

2. Which of these is the correct syntax to count numbers in range A1:A5?

easy

A. =COUNTNUM(A1:A5)

B. =SUM(A1:A5)

C. =COUNT(A1:A5)

D. =AVERAGE(A1:A5)

Solution

1. Step 1: Select the function that counts numeric cells

   COUNT is the correct function to count numeric cells in a range.

2. Step 2: Check syntax correctness

   =COUNT(A1:A5) is valid; others are either wrong function or invalid syntax.

3. Final Answer:

   =COUNT(A1:A5) -> Option C

4. Quick Check:

   COUNT counts numbers [OK]

Hint: COUNT counts numbers, SUM adds, AVERAGE averages [OK]

Common Mistakes:

• Using SUM instead of COUNT
• Typing COUNTNUM which doesn't exist
• Missing equal sign

3. Given cells A1=5, A2=10, A3=15, what is the result of =AVERAGE(A1:A3)?

medium

A. 30

B. 15

C. 5

D. 10

Solution

1. Step 1: Sum the values in A1 to A3

   5 + 10 + 15 = 30

2. Step 2: Divide sum by count of numbers

   There are 3 numbers, so 30 ÷ 3 = 10

3. Final Answer:

   10 -> Option D

4. Quick Check:

   Average = sum/count = 10 [OK]

Hint: Average = total sum divided by count [OK]

Common Mistakes:

• Adding but not dividing
• Dividing by wrong count
• Confusing with SUM result

4. You wrote =SUM(A1:A5 but get an error. What is the fix?

medium

A. Change SUM to COUNT

B. Add a closing parenthesis: =SUM(A1:A5)

C. Remove the equal sign

D. Add a comma after A5

Solution

1. Step 1: Check the formula for balanced parentheses

   The formula is missing a closing parenthesis which causes an error.

2. Step 2: Correct the formula syntax

   Adding the closing parenthesis fixes the error: =SUM(A1:A5)

3. Final Answer:

   Add a closing parenthesis: =SUM(A1:A5) -> Option B

4. Quick Check:

   Parentheses must be balanced [OK]

Hint: Always close parentheses in formulas [OK]

Common Mistakes:

• Forgetting closing parenthesis
• Changing function unnecessarily
• Removing equal sign

5. You have numbers in B1:B6: 4, 8, 12, 16, 20, and a text cell (e.g., 'N/A'). Which formula gives the average of only the numbers?

hard

A. =SUM(B1:B6)/COUNT(B1:B6)

B. =SUM(B1:B6)/COUNTA(B1:B6)

C. =SUM(B1:B6)/6

D. =COUNT(B1:B6)/SUM(B1:B6)

Solution

1. Step 1: Understand how COUNT and COUNTA handle text

   COUNT counts only numeric cells (ignores text), COUNTA counts non-empty cells (includes text).

2. Step 2: Check each formula

   A uses COUNTA (divides by 6, wrong); B uses COUNT (divides by 5, correct); C hardcodes /6 (wrong); D reverses operation.

3. Final Answer:

   =SUM(B1:B6)/COUNT(B1:B6) -> Option A

4. Quick Check:

   Average = sum ÷ count of numbers [OK]

Hint: Divide sum by count to get average safely [OK]

Common Mistakes:

• Using COUNTA instead of COUNT
• Dividing by total cells including non-numbers
• Reversing sum and count