Final project · Spreadsheet

Deliver a regional report

Project dataset

My saved work →

Project dataset Fresh dataset challenge →Each dataset keeps its own attempts.

Your task

Return region, order_count, revenue. Use one row per included region in ascending text order, then one Total row. Revenue is integer whole dollars.
Include paid orders with 2026-09-01 <= ordered_at < 2026-10-01 and quantity >= 0. The end date is excluded.
Collapse exact repeated order records. Exclude every record under a conflicting order_id. Empty region becomes Unknown.
Each eligible order contributes once to order_count. Revenue is quantity multiplied by unit_price; do not sum prices alone.

Pass every output check to complete this task. Suggested active time: 10 minutes. You can keep working after that.

Required output

Return exactly these columns in this order.

regionstring
order_countnumber
revenuenumber

Expected output: 5 rows. Keep the requested row order.

Values, data types and the number of copies of each row must match. A missing value is distinct from zero and from an empty string.

Empty CSV cells stay empty; the letters NULL are text. Apply the task’s declared replacement rules. Formulas are evaluated before checking.

Reconciliation checks

Raw records: 69; distinct eligible orders: 51.
The Total row must equal the sum of the regional counts and revenue: 51 orders and 2174 whole dollars.
The order on the first date belongs in the cohort; the one on the next month’s first date does not.

Need a next step?

Hint 1: where to start

Group first, then add the two regional totals.

Hint 2: build the method

Write down the eligible keys before aggregating. Inspect each anomaly against the output contract.

Hint 3: check your work

Use the reconciliation counts after constructing the complete output; a matching total alone does not prove the rows are correct.

How your work is checked

Every output check must pass. The score shows how much matched and helps you find mistakes; completion comes from passing all checks. Active time is recorded separately.

Passing records a correct practice output on this device. It is not a professional certification.

Source materials

69 original source records.

Data dictionary

order_id integer: Business key; reject all variants when any field conflicts.
region text: Empty means Unknown for this report.
status text: Only paid contributes.
quantity integer: Units sold; negative values are invalid here.
unit_price integer: Whole dollars per unit.
ordered_at text: ISO date; use a half-open reporting interval.

View source data

order_id,region,status,quantity,unit_price,ordered_at
2000,,pending,1,10,2026-09-01
2001,West,paid,2,11,2026-10-01
2002,North,paid,-1,12,2026-09-03
2003,East,paid,4,13,2026-09-01
2004,West,paid,5,14,2026-09-05
2005,North,paid,1,15,2026-09-06
2006,East,paid,2,16,2026-09-07
2007,West,pending,3,17,2026-09-08
2008,North,paid,4,18,2026-09-09
2009,East,paid,5,10,2026-09-10
2010,West,paid,1,11,2026-09-11
2011,,paid,2,12,2026-09-12
2012,East,paid,3,13,2026-09-13
2013,West,paid,4,14,2026-09-14
2014,North,pending,5,15,2026-09-15
2015,East,paid,1,16,2026-09-16
2016,West,paid,2,17,2026-09-17
2017,North,paid,3,18,2026-09-18
2018,East,paid,4,10,2026-09-19
2019,West,paid,5,11,2026-09-20
2020,North,paid,1,12,2026-09-21
2021,East,pending,2,13,2026-09-22
2022,,paid,3,14,2026-09-23
2023,North,paid,4,15,2026-09-24
2024,East,paid,5,16,2026-09-25
2025,West,paid,1,17,2026-09-26
2026,North,paid,2,18,2026-09-27
2027,East,paid,3,10,2026-09-28
2028,West,pending,4,11,2026-09-01
2029,North,paid,5,12,2026-09-02
2030,East,paid,1,13,2026-09-03
2031,West,paid,2,14,2026-09-04
2032,North,paid,3,15,2026-09-05
2033,,paid,4,16,2026-09-06
2034,West,paid,5,17,2026-09-07
2035,North,pending,1,18,2026-09-08
2036,East,paid,2,10,2026-09-09
2037,West,paid,3,11,2026-09-10
2038,North,paid,4,12,2026-09-11
2039,East,paid,5,13,2026-09-12
2040,West,paid,1,14,2026-09-13
2041,North,paid,2,15,2026-09-14
2042,East,pending,3,16,2026-09-15
2043,West,paid,4,17,2026-09-16
2044,,paid,5,18,2026-09-17
2045,East,paid,1,10,2026-09-18
2046,West,paid,2,11,2026-09-19
2047,North,paid,3,12,2026-09-20
2048,East,paid,4,13,2026-09-21
2049,West,pending,5,14,2026-09-22
2050,North,paid,1,15,2026-09-23
2051,East,paid,2,16,2026-09-24
2052,West,paid,3,17,2026-09-25
2053,North,paid,4,18,2026-09-26
2054,East,paid,5,10,2026-09-27
2055,,paid,1,11,2026-09-28
2056,North,pending,2,12,2026-09-01
2057,East,paid,3,13,2026-09-02
2058,West,paid,4,14,2026-09-03
2059,North,paid,5,15,2026-09-04
2060,East,paid,1,16,2026-09-05
2061,West,paid,2,17,2026-09-06
2062,North,paid,3,18,2026-09-07
2063,East,pending,4,10,2026-09-08
2004,West,paid,5,14,2026-09-05
2005,North,paid,1,15,2026-09-06
2006,East,paid,2,16,2026-09-07
2007,West,pending,3,17,2026-09-08
2010,West,paid,1,99,2026-09-11

Your work

Active 0:00 · Paused 0s

Edit the grid or the CSV. Formulas use grid row numbers, including the header row.

Business key

Browser formula support

This practice grid supports SUM, SUMIF, COUNTIF, IF, TRIM and an exact-match LOOKUP, plus arithmetic, comparisons, cell references and single-column ranges. For example: =SUM(B2:B3).

LOOKUP here returns the first exact match, or an empty value if none exists. Native Excel LOOKUP uses different matching rules. Excel workbook features, PivotTables and its full function library require Excel.

Keep missing values explicit. Arithmetic can convert an empty reference to zero; use a replacement only when the task permits it. Circular references are rejected.

CSV answer

Import answer

Keep a handoff you can replay

Check your current answer, then download the output and its report. The report includes the source, answer and each check so you can review what passed and what needs work.

To replay your work, open this task with the same dataset, import your answer and check it again.

Show the reference answer

region,order_count,revenue
East,17,636
North,15,684
Unknown,5,231
West,14,623
Total,51,2174

Build a unique eligible order list before grouping. Reconcile the count and quantity × price revenue with the Total row. Exclude the next month boundary.