Value aggregation (SUM, COUNT, AVG) in Google Sheets - Dashboard Guide

Dashboard Mode - Value aggregation (SUM, COUNT, AVG)

Dashboard Goal

See total sales, number of sales, and average sale amount for a small store.

Sample Data

Sale ID	Product	Amount
1	Apples	100
2	Bananas	200
3	Cherries	150
4	Dates	250
5	Elderberries	300

Dashboard Components

• Total Sales: Shows sum of all sales amounts.
  Formula: =SUM(C2:C6)
  Result: 1000
• Number of Sales: Counts how many sales entries exist.
  Formula: =COUNT(C2:C6)
  Result: 5
• Average Sale Amount: Calculates average sale amount.
  Formula: =AVERAGE(C2:C6)
  Result: 200
• Sales Data Table: Shows all sales records as is.

Dashboard Layout

+-------------------+---------------------+
|   Total Sales     |  Number of Sales    |
|      1000         |         5           |
+-------------------+---------------------+
|       Average Sale Amount (200)          |
+-----------------------------------------+
|           Sales Data Table               |
|  Sale ID | Product     | Amount          |
|    1     | Apples      | 100             |
|    2     | Bananas     | 200             |
|    3     | Cherries    | 150             |
|    4     | Dates       | 250             |
|    5     | Elderberries| 300             |
+-----------------------------------------+

Interactivity

If you add a filter by Product, the Total Sales, Number of Sales, and Average Sale Amount update to show only the filtered products. The Sales Data Table also updates to show only filtered rows.

Self Check

If you filter the Product column to show only "Bananas" and "Dates", what are the updated Total Sales, Number of Sales, and Average Sale Amount?

Answer: Total Sales = 450 (200 + 250), Number of Sales = 2, Average Sale Amount = 225

Key Result

Dashboard shows total, count, and average of sales amounts with a data table.

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