WHERE clause for filtering in Google Sheets - Dashboard Guide

Dashboard Mode - WHERE clause for filtering

Goal

Find sales records only for the East region to see how that area is performing.

Sample Data

Order ID	Region	Sales	Month
1001	East	200	Jan
1002	West	150	Jan
1003	East	300	Feb
1004	South	400	Feb
1005	East	250	Mar
1006	West	100	Mar

Dashboard Components

• Filtered Sales Table: Shows only rows where Region is East.
  Formula:
  =FILTER(A2:D7, B2:B7 = "East")
  Output:
  Order ID | Region | Sales | Month
  1001 | East | 200 | Jan
  1003 | East | 300 | Feb
  1005 | East | 250 | Mar
• Total Sales for East: Sum of sales from filtered data.
  Formula:
  =SUM(FILTER(C2:C7, B2:B7 = "East"))
  Output: 750
• Average Sales for East: Average sales from filtered data.
  Formula:
  =AVERAGE(FILTER(C2:C7, B2:B7 = "East"))
  Output: 250

Dashboard Layout

+-------------------------+------------------+
| Filtered Sales Table     | Total Sales (KPI) |
| (Filtered by East)      | Value: 750       |
+-------------------------+------------------+
| Average Sales (KPI)     |                  |
| Value: 250              |                  |
+-------------------------+------------------+

Interactivity

If you change the region in the filter formula (for example, from "East" to "West"), all components update automatically:

• The filtered table shows only sales from the selected region.
• Total sales and average sales KPIs recalculate for that region.

Self Check

Change the filter in the formula to "West". Which rows appear in the filtered table? What is the new total sales value?

Answer: Rows with Order ID 1002 and 1006 appear. Total sales is 150 + 100 = 250.

Key Result

Dashboard filters sales data by region using FILTER formula to show only East region sales and calculates total and average sales for East.