Cross-column conditional rules in Google Sheets - Dashboard Guide

Dashboard Mode - Cross-column conditional rules

Goal

Find and highlight sales records where the sales amount is greater than the target and the region is "East". This helps quickly spot top-performing sales in the East region.

Sample Data

Salesperson	Region	Sales Amount	Target
Alice	East	1200	1000
Bob	West	900	1000
Charlie	East	800	750
Diana	North	1100	1200
Eva	East	1300	1250

Dashboard Components

• KPI Card: Count of Sales Above Target in East Region
  Formula:
  =COUNTIFS(B2:B6, "East", C2:C6, ">"&D2:D6)
  Note: Google Sheets does not support array comparison in COUNTIFS directly, so use:
  =SUMPRODUCT((B2:B6="East")*(C2:C6>D2:D6))
  Result: 3
• Table: Highlight Rows Where Sales > Target and Region = East
  Conditional Formatting Rule (applied to A2:D6):
  =AND($B2="East", $C2>$D2)
  This highlights rows for Alice, Charlie, and Eva.
• Sum of Sales Amount for East Region Above Target
  Formula:
  =SUMPRODUCT((B2:B6="East")*(C2:C6>D2:D6)*C2:C6)
  Result: 3300

Dashboard Layout

+-----------------------------+-----------------------------+
| KPI: Count of Sales Above   | Sum of Sales Amount for East|
| Target in East Region (3)   | Region Above Target (3300)   |
+-----------------------------+-----------------------------+
|                                                             |
| Table: Sales Data with Conditional Highlighting             |
| (Rows where Sales > Target and Region = East are highlighted) |
|                                                             |
+-------------------------------------------------------------+

Interactivity

Add a filter dropdown for Region. When you select a region (e.g., East), the KPI card, sum, and table update to show only data for that region. This helps focus on specific regions easily.

Self Check

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

• KPI Card: Count of Sales Above Target in West Region -> 0
• Sum of Sales Amount for West Region Above Target -> 0
• Table: Only rows with Region = West shown, no rows highlighted since sales < target

Key Result

Dashboard shows count and sum of sales above target in East region with conditional highlighting and region filter.