Establish the answer before asking for a formula
Use the prepared 13-line sample and the week October 26 through before November 2, 2026. The expected net sales are RM267.60. The original 14-record file contains an exact repeat; a correct formula cannot compensate for every source problem unless that behavior is explicitly included in its specification.
Work with the example
Prepared sample ↓Expected included lines ↓Checked workbook ↓All records are fictional. Import CSV identifier columns as Text. Quoted CSV fields do not define spreadsheet column types.
The examples below are authored instructional candidates, not a benchmark of ChatGPT, Claude, Gemini, or any particular model. No product-specific accuracy or speed claim is made. You can run the same test with whichever assistant you use.
Give the assistant a precise specification
I use an Excel table named Orders with these columns:
order_date (real date), status (text), net_rm (numeric).
The source has already been reviewed for repeated line records.
Start date is B2. End date is C2 and is exclusive.
Sum net_rm for Shipped records with date >= B2 and date < C2.
Give one formula, state assumptions, and list boundary tests.
Use this fictional data only. Do not infer missing values.Separating source cleanup from the requested calculation makes the task inspectable. Include the spreadsheet version and locale if function availability or separators matter. Do not provide a real customer export when a small fictional example is enough to test the formula.
Reject a formula that omits a required condition
This deliberately incomplete candidate sums every Shipped line, regardless of date:
=SUMIFS(Orders[net_rm],Orders[status],"Shipped")On the prepared sample it includes the RM35.00 record before the week and the RM25.00 record after it. Its result is RM327.60, which is RM60.00 too high. A plausible function name and a valid formula do not establish that the specification was implemented.
A candidate matching the stated conditions is:
=SUMIFS(Orders[net_rm],Orders[status],"Shipped",
Orders[order_date],">="&B2,Orders[order_date],"<"&C2)This structured-reference example assumes you have created that table and those columns. The downloadable workbook uses bounded ranges with the same eligibility rule, so its formulas can be traced directly.
Exercise the boundaries and change an input
| Test case | Expected behavior |
|---|---|
| Shipped on the start date | Included. |
| Shipped on the exclusive end date | Excluded. |
| Pending inside the week | Excluded. |
| Shipped with zero net value | Included as a valid zero. |
| A repeated raw source line | Needs cleanup before this formula. |
| A date stored as text | Needs a reviewed type conversion. |
Then change one known quantity in the worked workbook. Orders G6 from 2 to 3 should increase weekly net sales from RM267.60 to RM280.10. Restore it after the check. Record the exact prompt, tool, date, output, and corrections when you run your own experiment. Retain unsuccessful outputs too, so your conclusion reflects what actually happened.
Sources & further reading
The sample data and worked calculations are original instructional examples. Spreadsheet menus and supported features depend on your installed version. See the verification method for the checks performed.
Continue the workflow