Combining clauses in Google Sheets - Dashboard Guide

Dashboard Mode - Combining clauses

Dashboard Goal

Find sales records that meet multiple conditions, such as sales above a certain amount and from a specific region, to help focus on important data.

Sample Data

Order ID	Region	Salesperson	Sales Amount	Month
1001	East	Alice	500	January
1002	West	Bob	300	January
1003	East	Charlie	700	February
1004	South	Diana	200	February
1005	East	Eva	450	March
1006	West	Frank	650	March
1007	South	Grace	400	March

Dashboard Components

• KPI Card: Total Sales Above $400 in East Region
  Formula: =COUNTIFS(B2:B8, "East", D2:D8, ">400")
  Result: 3
• Filtered Sales Table
  Formula: =FILTER(A2:E8, (B2:B8 = "East") * (D2:D8 > 400))
  Result:
  

  Order ID	Region	Salesperson	Sales Amount	Month
  1001	East	Alice	500	January
  1003	East	Charlie	700	February
  1005	East	Eva	450	March

• Average Sales Amount for East Region Above $400
  Formula: =AVERAGE(FILTER(D2:D8, (B2:B8 = "East") * (D2:D8 > 400)))
  Result: 550

Dashboard Layout

+-------------------------------+
| KPI: Total Sales Above $400   |
| in East Region (3)            |
+-------------------------------+
| Filtered Sales Table (3 rows) |
|                               |
| Order ID | Region | Sales ...  |
| 1001     | East   | 500       |
| 1003     | East   | 700       |
| 1005     | East   | 450       |
+-------------------------------+
| Average Sales: $550            |
+-------------------------------+

Interactivity

Add a filter control for Region and Sales Amount. When you select a region or set a minimum sales amount, the KPI card, filtered table, and average sales update automatically to show only matching records.

Self Check

If you add a filter for Region = West and Sales Amount > 400, which components update and what are their new values?

• KPI Card: Counts sales in West region above $400 -> 1
• Filtered Sales Table: Shows only West region sales above $400 -> Order ID 1006
• Average Sales: Average of filtered sales -> 650

Key Result

Dashboard shows sales records filtered by region and sales amount using combined conditions.