AVERAGEIF and AVERAGEIFS in Google Sheets - Dashboard Guide

Dashboard Mode - AVERAGEIF and AVERAGEIFS

Dashboard Goal

Find average sales based on one or more conditions to understand performance by region and product type.

Sample Data

Order ID	Region	Product	Sales
1001	East	Widget	120
1002	West	Gadget	200
1003	East	Gadget	150
1004	North	Widget	100
1005	South	Widget	180
1006	West	Widget	220
1007	East	Gadget	130

Dashboard Components

• KPI 1: Average Sales in East Region
  Formula: =AVERAGEIF(B2:B8, "East", D2:D8)
  Result: 133.33
• KPI 2: Average Sales for Widget Products
  Formula: =AVERAGEIF(C2:C8, "Widget", D2:D8)
  Result: 155
• KPI 3: Average Sales for Gadget in East Region
  Formula: =AVERAGEIFS(D2:D8, B2:B8, "East", C2:C8, "Gadget")
  Result: 140
• Table: Sales Data
  Shows all orders with region, product, and sales.

Dashboard Layout

+---------------------------+---------------------------+
| Average Sales East Region | Average Sales Widget Prod |
|         (KPI 1)           |          (KPI 2)           |
+---------------------------+---------------------------+
|      Average Sales Gadget in East Region (KPI 3)      |
+-------------------------------------------------------+
|                    Sales Data Table                    |
+-------------------------------------------------------+

Interactivity

Add a filter for Region or Product. When you select a region or product, the KPIs update to show averages only for the selected items. The sales data table also filters to show matching rows.

Self Check

If you add a filter to show only Region = West, which components update and what are their new values?

• KPI 1: Average Sales in East Region - Updates to no data (blank or error) because East is filtered out.
• KPI 2: Average Sales for Widget Products - Updates to average sales of Widget in West region: 220.
• KPI 3: Average Sales for Gadget in East Region - Updates to no data (blank or error) because East is filtered out.
• Sales Data Table - Shows only rows with Region = West (Order IDs 1002 and 1006).

Key Result

Dashboard shows average sales by region and product using AVERAGEIF and AVERAGEIFS formulas.