Create a dynamic dashboard

What you will build

Create a lightweight region dashboard from a source order table. This lesson uses fictional sample data so you can reproduce each action.

Learning objective

Complete the steps using the dataset below, explain why the feature or formula is used, and recognize issues when inputs change.

Prepare the sample data

Paste this example as tab-separated values beginning at A1 on a new worksheet:

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Source data A1:C5 in the current Google Sheets practice workbook before the dashboard area is built.

Work through the example

Step 1: Keep the source data in A1:C5. In E1:G3, create a dashboard summary with headers Region, Paid Amount, and Paid Orders; enter North in E2 and South in E3.

Step 2: In F2, enter =SUMIFS($B$2:$B$5,$A$2:$A$5,E2,$C$2:$C$5,"Paid"). In G2, enter =COUNTIFS($A$2:$A$5,E2,$C$2:$C$5,"Paid"). Fill both formulas down through row 3 so each region uses the label in column E.

Step 3: Verify North = 200 and 2, and South = 320 and 1. Then change C3 from Open to Paid: South should recalculate to 560 and 2. Restore C3 to Open and confirm South returns to 320 and 1.

Formula to try

In the current practice workbook, put SUMIFS in F2 and COUNTIFS in G2, then fill both formulas down through row 3. Keep A1:C5 as the source range and compare E1:G3 with the checkpoint.

=SUMIFS($B$2:$B$5,$A$2:$A$5,E2,$C$2:$C$5,"Paid")

Cell F2 selected with the SUMIFS formula visible in the formula bar and North Paid Amount calculated as 200.

How this formula works=SUMIFS($B$2:$B$5,$A$2:$A$5,E2,$C$2:$C$5,"Paid")

Overall meaning of the whole formula
- Add values in B2:B5 for rows where column A matches E2 and column C is "Paid".
SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
- `sum_range` is required; it accepts the numeric range to add. In this lesson the argument passed is `$B$2:$B$5`.
- `criteria_range1` is required; it accepts a range whose cells are tested. In this lesson the argument passed is `$A$2:$A$5`.
- `criterion1` is required; it accepts a value, cell reference, or condition text matched against the preceding criteria range. In this lesson the argument passed is `E2`.
- `criteria_range2` is optional; it accepts a range whose cells are tested. In this lesson the argument passed is `$C$2:$C$5`.
- `criterion2` is optional; it accepts a value, cell reference, or condition text matched against the preceding criteria range. In this lesson the argument passed is `"Paid"`.
Whole-expression evaluation
- SUMIFS adds values from the first range only for rows that satisfy every criterion.
- $B$2:$B$5 is the Amount range; the $ signs keep it fixed when the formula is filled down.
- $A$2:$A$5,E2 requires the source Region to match the dashboard label in E2.
- $C$2:$C$5,"Paid" requires Status to equal Paid.
- With E2 = North, the two Paid amounts are 120 and 80, so F2 = 200.
Why the displayed final result appears
- With E2 = North, the two Paid amounts are 120 and 80, so F2 = 200.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

=COUNTIFS($A$2:$A$5,E2,$C$2:$C$5,"Paid")

Cell G2 selected with the COUNTIFS formula visible in the formula bar and North Paid Orders calculated as 2.

How this formula works=COUNTIFS($A$2:$A$5,E2,$C$2:$C$5,"Paid")

Overall meaning of the whole formula
- Count rows where column A matches E2 and column C is "Paid".
COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])
- `criteria_range1` is required; it accepts a range whose cells are tested. In this lesson the argument passed is `$A$2:$A$5`.
- `criterion1` is required; it accepts a value, cell reference, or condition text matched against the preceding criteria range. In this lesson the argument passed is `E2`.
- `criteria_range2` is optional; it accepts a range whose cells are tested. In this lesson the argument passed is `$C$2:$C$5`.
- `criterion2` is optional; it accepts a value, cell reference, or condition text matched against the preceding criteria range. In this lesson the argument passed is `"Paid"`.
Whole-expression evaluation
- COUNTIFS counts rows that satisfy all supplied criteria.
- $A$2:$A$5,E2 filters by the region label in E2.
- $C$2:$C$5,"Paid" keeps only rows whose Status is Paid.
- With E2 = North, two rows match, so G2 = 2.
- When filled down to row 3, only E2 changes to E3; the source ranges remain fixed.
Why the displayed final result appears
- When filled down to row 3, only E2 changes to E3; the source ranges remain fixed.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

Checkpoint

Region	Paid Amount	Paid Orders
North	200	2
South	320	1

Checkpoint after restoring the sample: North = 200 and 2; South = 320 and 1. Dynamic test verified: changing C3 from Open to Paid recalculates South to 560 and 2; restoring C3 to Open returns it to 320 and 1.

Final E1:G3 dashboard beside source data A1:C5, showing North 200/2 and South 320/1 after the sample is restored.

Common mistake and correction

If the result is unexpected, check that the source ranges are locked with $, E2:E3 contain North/South, and the Status text is spelled exactly Paid/Open. If the source ranges shift while filling down, the South row will be wrong.

Independent practice

After the example, change C3 from Open to Paid and predict the South row before checking it. Paid Amount should rise from 320 to 560 and Paid Orders from 1 to 2. Then restore C3 to Open.

Key takeaways

✓Keep the source table separate from the dashboard area so formulas and results are easy to audit.
✓Use SUMIFS to total Amount by Region and Paid status.
✓Use COUNTIFS to count Paid orders by Region.
✓Lock source ranges with absolute references before filling formulas down.
✓Use a controlled input change to prove the dashboard recalculates, then restore the sample.

Continue learning

Previous lesson: Calculate running totals; Next lesson: Calculate working days.

What you will build

Create a lightweight region dashboard from a source order table. This lesson uses fictional sample data so you can reproduce each action.

Learning objective

Complete the steps using the dataset below, explain why the feature or formula is used, and recognize issues when inputs change.

Prepare the sample data

Paste this example as tab-separated values beginning at A1 on a new worksheet:

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Work through the example

Step 1: Keep the source data in A1:C5. In E1:G3, create a dashboard summary with headers Region, Paid Amount, and Paid Orders; enter North in E2 and South in E3.

Step 3: Verify North = 200 and 2, and South = 320 and 1. Then change C3 from Open to Paid: South should recalculate to 560 and 2. Restore C3 to Open and confirm South returns to 320 and 1.

Formula to try

In the current practice workbook, put SUMIFS in F2 and COUNTIFS in G2, then fill both formulas down through row 3. Keep A1:C5 as the source range and compare E1:G3 with the checkpoint.

=SUMIFS($B$2:$B$5,$A$2:$A$5,E2,$C$2:$C$5,"Paid")

How this formula works=SUMIFS($B$2:$B$5,$A$2:$A$5,E2,$C$2:$C$5,"Paid")

Overall meaning of the whole formula
- Add values in B2:B5 for rows where column A matches E2 and column C is "Paid".
SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
- `sum_range` is required; it accepts the numeric range to add. In this lesson the argument passed is `$B$2:$B$5`.
- `criteria_range1` is required; it accepts a range whose cells are tested. In this lesson the argument passed is `$A$2:$A$5`.
- `criterion1` is required; it accepts a value, cell reference, or condition text matched against the preceding criteria range. In this lesson the argument passed is `E2`.
- `criteria_range2` is optional; it accepts a range whose cells are tested. In this lesson the argument passed is `$C$2:$C$5`.
- `criterion2` is optional; it accepts a value, cell reference, or condition text matched against the preceding criteria range. In this lesson the argument passed is `"Paid"`.
Whole-expression evaluation
- SUMIFS adds values from the first range only for rows that satisfy every criterion.
- $B$2:$B$5 is the Amount range; the $ signs keep it fixed when the formula is filled down.
- $A$2:$A$5,E2 requires the source Region to match the dashboard label in E2.
- $C$2:$C$5,"Paid" requires Status to equal Paid.
- With E2 = North, the two Paid amounts are 120 and 80, so F2 = 200.
Why the displayed final result appears
- With E2 = North, the two Paid amounts are 120 and 80, so F2 = 200.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

=COUNTIFS($A$2:$A$5,E2,$C$2:$C$5,"Paid")

How this formula works=COUNTIFS($A$2:$A$5,E2,$C$2:$C$5,"Paid")

Overall meaning of the whole formula
- Count rows where column A matches E2 and column C is "Paid".
COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])
- `criteria_range1` is required; it accepts a range whose cells are tested. In this lesson the argument passed is `$A$2:$A$5`.
- `criterion1` is required; it accepts a value, cell reference, or condition text matched against the preceding criteria range. In this lesson the argument passed is `E2`.
- `criteria_range2` is optional; it accepts a range whose cells are tested. In this lesson the argument passed is `$C$2:$C$5`.
- `criterion2` is optional; it accepts a value, cell reference, or condition text matched against the preceding criteria range. In this lesson the argument passed is `"Paid"`.
Whole-expression evaluation
- COUNTIFS counts rows that satisfy all supplied criteria.
- $A$2:$A$5,E2 filters by the region label in E2.
- $C$2:$C$5,"Paid" keeps only rows whose Status is Paid.
- With E2 = North, two rows match, so G2 = 2.
- When filled down to row 3, only E2 changes to E3; the source ranges remain fixed.
Why the displayed final result appears
- When filled down to row 3, only E2 changes to E3; the source ranges remain fixed.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

Checkpoint

Region	Paid Amount	Paid Orders
North	200	2
South	320	1

Common mistake and correction

Independent practice

After the example, change C3 from Open to Paid and predict the South row before checking it. Paid Amount should rise from 320 to 560 and Paid Orders from 1 to 2. Then restore C3 to Open.

Key takeaways

✓Keep the source table separate from the dashboard area so formulas and results are easy to audit.
✓Use SUMIFS to total Amount by Region and Paid status.
✓Use COUNTIFS to count Paid orders by Region.
✓Lock source ranges with absolute references before filling formulas down.
✓Use a controlled input change to prove the dashboard recalculates, then restore the sample.

Continue learning

Previous lesson: Calculate running totals; Next lesson: Calculate working days.

Create a dynamic dashboard

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Formula to try

Checkpoint

Common mistake and correction

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments

Create a dynamic dashboard

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Formula to try

Checkpoint

Common mistake and correction

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments