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.