Bird
Raised Fist0
Google Sheetsspreadsheet~8 mins

Cross-column conditional rules in Google Sheets - Dashboard Guide

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash

Start learning this pattern below

Jump into concepts and practice - no test required

or
Recommended
Test this pattern10 questions across easy, medium, and hard to know if this pattern is strong
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
SalespersonRegionSales AmountTarget
AliceEast12001000
BobWest9001000
CharlieEast800750
DianaNorth11001200
EvaEast13001250
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.

Practice

(1/5)
1. In Google Sheets, you want to check if the value in column A is greater than the value in column B for the same row. Which formula correctly returns TRUE or FALSE for this condition in row 2?
easy
A. =A2>B2
B. =IF(A2, B2)
C. =A2+B2
D. =IF(A2<B2, TRUE, FALSE)

Solution

  1. Step 1: Understand the comparison needed

    The question asks to check if the value in column A is greater than column B in the same row, which is a simple comparison.

  2. Step 2: Identify the correct formula syntax

    The formula =A2>B2 directly compares the two cells and returns TRUE or FALSE accordingly.

  3. Final Answer:

    =A2>B2 -> Option A

  4. Quick Check:

    Simple comparison formula =A2>B2 [OK]
Hint: Use direct comparison like =A2>B2 for cross-column checks [OK]
Common Mistakes:
  • Using IF without condition like IF(A2, B2)
  • Adding values instead of comparing
  • Using wrong comparison operators
2. Which of the following formulas correctly applies a conditional rule to return "Yes" if the value in column C is equal to the value in column D, otherwise "No", for row 3?
easy
A. =IF(C3=D3, "Yes", "No")
B. =IF(C3==D3, "Yes", "No")
C. =IF(C3=D3, Yes, No)
D. =IF(C3<>D3, "Yes", "No")

Solution

  1. Step 1: Understand the equality check syntax in Google Sheets

    Google Sheets uses a single equals sign = for comparison inside IF, not double equals.

  2. Step 2: Check the correct use of text strings in IF

    Text values must be in quotes, so "Yes" and "No" are correct.

  3. Final Answer:

    =IF(C3=D3, "Yes", "No") -> Option A

  4. Quick Check:

    Use single = and quotes for text in IF [OK]
Hint: Use single = for comparison and quote text in IF [OK]
Common Mistakes:
  • Using double == which causes error
  • Not quoting text strings
  • Using <> instead of = for equality
3. Given the formula =IF(AND(A2>10, B2<5), "Pass", "Fail") in cell C2, what will be the result if A2=12 and B2=3?
medium
A. TRUE
B. "Fail"
C. "Pass"
D. FALSE

Solution

  1. Step 1: Evaluate the AND condition with given values

    A2>10 is TRUE because 12>10, and B2<5 is TRUE because 3<5. AND(TRUE, TRUE) returns TRUE.

  2. Step 2: Apply IF based on AND result

    Since AND is TRUE, IF returns "Pass" as specified.

  3. Final Answer:

    "Pass" -> Option C

  4. Quick Check:

    AND(TRUE, TRUE) = TRUE, so IF returns "Pass" [OK]
Hint: Check each condition inside AND before IF [OK]
Common Mistakes:
  • Confusing AND logic with OR
  • Ignoring comparison operators
  • Expecting TRUE/FALSE instead of text output
4. You wrote this formula in cell D2: =IF(A2>B2, "Higher", "Lower"). But it always shows "Lower" even when A2 is greater than B2. What is the likely problem?
medium
A. The formula uses wrong comparison operator
B. The IF function syntax is incorrect
C. The formula needs to be entered as an array formula
D. Cells A2 or B2 contain text, not numbers

Solution

  1. Step 1: Understand why comparison might fail

    If A2 or B2 contain text (like numbers stored as text), the comparison A2>B2 may not work as expected.

  2. Step 2: Check formula syntax and usage

    The formula syntax is correct and does not require array formula. The issue is likely data type mismatch.

  3. Final Answer:

    Cells A2 or B2 contain text, not numbers -> Option D

  4. Quick Check:

    Text in number cells breaks numeric comparisons [OK]
Hint: Check cell data types if comparisons fail [OK]
Common Mistakes:
  • Assuming syntax error without checking data
  • Trying array formula unnecessarily
  • Using wrong operators like ==
5. You want to create a formula in column E that labels each row as "OK" if the value in column A is greater than column B and the value in column C is not empty. Otherwise, it should show "Check". Which formula correctly does this for row 2?
hard
A. =IF(A2>B2, IF(C2="", "OK", "Check"), "Check")
B. =IF(AND(A2>B2, C2<>""), "OK", "Check")
C. =IF(AND(A2>B2, NOT(ISBLANK(C2))), "OK", "Check")
D. =IF(OR(A2>B2, C2<>""), "OK", "Check")

Solution

  1. Step 1: Understand the conditions to combine

    The label "OK" requires both: A2>B2 and C2 not empty. This means both conditions must be true, so AND is needed.

  2. Step 2: Check formula options for correct logic

    =IF(AND(A2>B2, C2<>""), "OK", "Check") uses AND with A2>B2 and C2<>"" which means C2 is not empty. This matches the requirement exactly.

  3. Final Answer:

    =IF(AND(A2>B2, C2<>""), "OK", "Check") -> Option B

  4. Quick Check:

    Use AND for all conditions, check non-empty with <>"" [OK]
Hint: Use AND and <>"" to check multiple conditions and non-empty cells [OK]
Common Mistakes:
  • Using OR instead of AND
  • Using ISBLANK incorrectly
  • Nesting IF unnecessarily