Sales dashboard

What you will build

Build a small sales dashboard with Paid revenue and Paid order count by Region. You will verify both the initial results and how the dashboard responds when an order status changes.

Learning objective

Use SUMIFS to add Amount by Region and Status, use COUNTIFS to count orders under the same conditions, and compare the results with the Dashboard table.

Prepare the sample data

Open the practice workbook or make a copy. The sample contains four orders. Before entering formulas, open Raw Sales and compare its four source rows with the screenshot below; Example contains the Formula lab, and Dashboard summarizes the results by Region.

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.

The Raw Sales tab in the _result workbook showing the four source rows O01–O04; O02 has Status = Open.

Open the practice workbook in Google Sheets ↗

Calculate Paid revenue for the selected Region

Step 1: On Example, keep E2 = North. Select H2 and enter the SUMIFS formula below. The screenshot after the explanation shows H2 selected, the complete formula in the formula bar, and the calculated result 200.

=SUMIFS('Raw Sales'!$C$2:$C$5,'Raw Sales'!$B$2:$B$5,E2,'Raw Sales'!$D$2:$D$5,"Paid")

How this formula works=SUMIFS('Raw Sales'!$C$2:$C$5,'Raw Sales'!$B$2:$B$5,E2,'Raw Sales'!$D$2:$D$5,"Paid")

Overall meaning of the whole formula
- Add the Amount values for orders whose Region matches the Region selected in E2 and whose Status is "Paid". With the current sample data and E2 = North, the formula adds 120 from O01 and 80 from O03 to return 200.
SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
- sum_range is required; it accepts the numeric range to add. This lesson passes 'Raw Sales'!$C$2:$C$5, the Amount values {120; 240; 80; 320}.
- criteria_range1 is required; this lesson passes 'Raw Sales'!$B$2:$B$5, the Region values {North; South; North; South}.
- criterion1 is required; this lesson passes E2. With E2 = North, only O01 and O03 pass the Region test.
- criteria_range2 and criterion2 form an optional additional criteria pair; this lesson passes 'Raw Sales'!$D$2:$D$5 and "Paid".
- sum_range and every criteria_range contain the same four data rows, so their elements align correctly by row.
Whole-expression evaluation
- O01: Region is North and Status is Paid, so Amount 120 is included.
- O02: Region is South, so it is excluded even though Amount is 240.
- O03: Region is North and Status is Paid, so Amount 80 is included.
- O04: Region is South, so it is excluded.
- The final sum is 120 + 80 = 200.
Why the displayed final result appears
- H2 displays 200 because only O01 and O03 satisfy both Region = North and Status = Paid.
- The formula reads directly from Raw Sales; the Raw Sales screenshot above provides the visible source rows referenced by the formula.

The Example tab with H2 selected; the formula bar shows the complete SUMIFS formula referencing Raw Sales and H2 returns 200.

Count Paid orders for the same Region

Step 2: Select H3 and enter the COUNTIFS formula below. The screenshot after the explanation shows H3 selected, the full formula in the formula bar, and the calculated count 2.

=COUNTIFS('Raw Sales'!$B$2:$B$5,E2,'Raw Sales'!$D$2:$D$5,"Paid")

How this formula works=COUNTIFS('Raw Sales'!$B$2:$B$5,E2,'Raw Sales'!$D$2:$D$5,"Paid")

Overall meaning of the whole formula
- Count the orders whose Region matches the Region selected in E2 and whose Status is "Paid". With the current sample data and E2 = North, only O01 and O03 satisfy both conditions, so the result is 2.
COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])
- criteria_range1 is required; this lesson passes 'Raw Sales'!$B$2:$B$5.
- criterion1 is required; this lesson passes E2 = North.
- criteria_range2 and criterion2 form an optional additional criteria pair; this lesson passes 'Raw Sales'!$D$2:$D$5 and "Paid".
- Both criteria ranges contain four rows, so Region and Status conditions align with the same order row.
Whole-expression evaluation
- O01 matches North + Paid, so it is counted.
- O02 does not match North, so it is not counted.
- O03 matches North + Paid, so it is counted.
- O04 does not match North, so it is not counted.
- Exactly 2 rows satisfy all conditions.
Why the displayed final result appears
- H3 displays 2 because O01 and O03 are the only orders with both Region = North and Status = Paid.
- SUMIFS and COUNTIFS use the same source conditions, but SUMIFS adds Amount while COUNTIFS counts matching rows.

The Example tab with H3 selected; the formula bar shows the complete COUNTIFS formula referencing Raw Sales and H3 returns 2.

Check the Dashboard summary

Step 3: Open the Dashboard tab. With the original sample data, North has Paid amount = 200 and Paid order count = 2; South has Paid amount = 320 and Paid order count = 1.

The Dashboard tab after restoring the source data: North has Paid amount 200 and Paid order count 2; South has 320 and 1.

Test a changed status

Step 4: On Raw Sales, temporarily change D3 for order O02 from Open to Paid. Dashboard should recalculate South from 320 / 1 to 560 / 2; the screenshot below shows that changed Dashboard state. After checking it, restore D3 to Open to return to the sample data.

The Dashboard after O02 is temporarily changed to Paid: North remains 200 and 2; South updates to Paid amount 560 and Paid order count 2.

Checkpoint

Order	Region	Amount	Status
O01	North	120	Paid
O02	South	240	Open
O03	North	80	Paid
O04	South	320	Paid

This is the source state that should be restored after the changed-input test.

Region	Paid amount	Paid order count
North	200	2
South	320	1

In the sample state, Dashboard should show these two rows. When O02 is temporarily changed to Paid, South should become 560 and 2.

Common mistakes and fixes

Do not manually edit Dashboard numbers to force a match. Fix the source data or formulas and let Google Sheets recalculate the result.

Independent practice

Repeat the O02 test once more: change Open to Paid, confirm South = 560 and 2, then restore Open and confirm South returns to 320 and 1.

Key takeaways

✓SUMIFS adds Amount under multiple conditions.
✓COUNTIFS counts rows under the same set of conditions.
✓For North, the sample data produces Paid revenue = 200 and Paid order count = 2.
✓For South, the sample state produces 320 and 1; changing O02 to Paid makes the results 560 and 2.
✓After a changed-input test, restore Raw Sales to the sample state.

Continue learning

Previous lesson: Expense tracker; Next lesson: —.

What you will build

Build a small sales dashboard with Paid revenue and Paid order count by Region. You will verify both the initial results and how the dashboard responds when an order status changes.

Learning objective

Use SUMIFS to add Amount by Region and Status, use COUNTIFS to count orders under the same conditions, and compare the results with the Dashboard table.

Prepare the sample data

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.

Open the practice workbook in Google Sheets ↗

Calculate Paid revenue for the selected Region

=SUMIFS('Raw Sales'!$C$2:$C$5,'Raw Sales'!$B$2:$B$5,E2,'Raw Sales'!$D$2:$D$5,"Paid")

How this formula works=SUMIFS('Raw Sales'!$C$2:$C$5,'Raw Sales'!$B$2:$B$5,E2,'Raw Sales'!$D$2:$D$5,"Paid")

Overall meaning of the whole formula
- Add the Amount values for orders whose Region matches the Region selected in E2 and whose Status is "Paid". With the current sample data and E2 = North, the formula adds 120 from O01 and 80 from O03 to return 200.
SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
- sum_range is required; it accepts the numeric range to add. This lesson passes 'Raw Sales'!$C$2:$C$5, the Amount values {120; 240; 80; 320}.
- criteria_range1 is required; this lesson passes 'Raw Sales'!$B$2:$B$5, the Region values {North; South; North; South}.
- criterion1 is required; this lesson passes E2. With E2 = North, only O01 and O03 pass the Region test.
- criteria_range2 and criterion2 form an optional additional criteria pair; this lesson passes 'Raw Sales'!$D$2:$D$5 and "Paid".
- sum_range and every criteria_range contain the same four data rows, so their elements align correctly by row.
Whole-expression evaluation
- O01: Region is North and Status is Paid, so Amount 120 is included.
- O02: Region is South, so it is excluded even though Amount is 240.
- O03: Region is North and Status is Paid, so Amount 80 is included.
- O04: Region is South, so it is excluded.
- The final sum is 120 + 80 = 200.
Why the displayed final result appears
- H2 displays 200 because only O01 and O03 satisfy both Region = North and Status = Paid.
- The formula reads directly from Raw Sales; the Raw Sales screenshot above provides the visible source rows referenced by the formula.

Count Paid orders for the same Region

Step 2: Select H3 and enter the COUNTIFS formula below. The screenshot after the explanation shows H3 selected, the full formula in the formula bar, and the calculated count 2.

=COUNTIFS('Raw Sales'!$B$2:$B$5,E2,'Raw Sales'!$D$2:$D$5,"Paid")

How this formula works=COUNTIFS('Raw Sales'!$B$2:$B$5,E2,'Raw Sales'!$D$2:$D$5,"Paid")

Overall meaning of the whole formula
- Count the orders whose Region matches the Region selected in E2 and whose Status is "Paid". With the current sample data and E2 = North, only O01 and O03 satisfy both conditions, so the result is 2.
COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])
- criteria_range1 is required; this lesson passes 'Raw Sales'!$B$2:$B$5.
- criterion1 is required; this lesson passes E2 = North.
- criteria_range2 and criterion2 form an optional additional criteria pair; this lesson passes 'Raw Sales'!$D$2:$D$5 and "Paid".
- Both criteria ranges contain four rows, so Region and Status conditions align with the same order row.
Whole-expression evaluation
- O01 matches North + Paid, so it is counted.
- O02 does not match North, so it is not counted.
- O03 matches North + Paid, so it is counted.
- O04 does not match North, so it is not counted.
- Exactly 2 rows satisfy all conditions.
Why the displayed final result appears
- H3 displays 2 because O01 and O03 are the only orders with both Region = North and Status = Paid.
- SUMIFS and COUNTIFS use the same source conditions, but SUMIFS adds Amount while COUNTIFS counts matching rows.

Check the Dashboard summary

Step 3: Open the Dashboard tab. With the original sample data, North has Paid amount = 200 and Paid order count = 2; South has Paid amount = 320 and Paid order count = 1.

Test a changed status

Checkpoint

Order	Region	Amount	Status
O01	North	120	Paid
O02	South	240	Open
O03	North	80	Paid
O04	South	320	Paid

This is the source state that should be restored after the changed-input test.

Region	Paid amount	Paid order count
North	200	2
South	320	1

In the sample state, Dashboard should show these two rows. When O02 is temporarily changed to Paid, South should become 560 and 2.

Common mistakes and fixes

Do not manually edit Dashboard numbers to force a match. Fix the source data or formulas and let Google Sheets recalculate the result.

Independent practice

Repeat the O02 test once more: change Open to Paid, confirm South = 560 and 2, then restore Open and confirm South returns to 320 and 1.

Key takeaways

✓SUMIFS adds Amount under multiple conditions.
✓COUNTIFS counts rows under the same set of conditions.
✓For North, the sample data produces Paid revenue = 200 and Paid order count = 2.
✓For South, the sample state produces 320 and 1; changing O02 to Paid makes the results 560 and 2.
✓After a changed-input test, restore Raw Sales to the sample state.

Continue learning

Previous lesson: Expense tracker; Next lesson: —.

Sales dashboard

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Calculate Paid revenue for the selected Region

Count Paid orders for the same Region

Check the Dashboard summary

Test a changed status

Checkpoint

Common mistakes and fixes

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments

Sales dashboard

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Calculate Paid revenue for the selected Region

Count Paid orders for the same Region

Check the Dashboard summary

Test a changed status

Checkpoint

Common mistakes and fixes

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments