Spreadsheet formula builder with explanation

Describe what you want in plain English and get a working Excel or Google Sheets formula, how it works, and edge cases.

Change note: Initial version

Prompt

Write a {{app}} formula for this task.

What I want to calculate:
{{task}}

My sheet layout (columns, headers, example rows):
{{layout}}

Where the result goes: {{result_location}}

Requirements:
- Give the single best formula first, ready to paste, using the exact cell references and ranges from my layout.
- Prefer approaches that work in the current version of {{app}}. If a modern function is much better but may not be available to everyone, give a fallback version too.
- Explain how the formula works in 3-5 short bullet points, one per function or step.
- Test it on 2-3 rows from my example and show the expected result for each.
- List edge cases (blanks, text in number columns, duplicates, errors) and say how the formula behaves for each. Wrap it in error handling only if that makes sense, and say why.
- If my layout is ambiguous or you must assume something, say so before the formula and ask one concise question at the end.

Format: Formula, How it works, Worked check, Edge cases, Alternative (only if meaningfully different).

Variables

• {{app}}Spreadsheet appselect

  Options: Excel, Google Sheets

  Default: Excel

• {{task}}What you needlong

  Default: Sum the sales amount for each region, but only for orders with status 'Shipped' placed in the last 30 days.

  Plain English is fine.

• {{layout}}Sheet layoutlong

  Default: Sheet 'Orders': A = Order date, B = Region, C = Status, D = Amount. Headers in row 1, data in rows 2-500.

  Which columns hold what, plus a couple of example rows.

• {{result_location}}Where the result goestext

  Default: A summary table on the 'Summary' sheet, one region per row in column A

Example output

**Assumptions**: Dates in column A are real dates (not text). Region names are in Summary!A2:A6.

**Formula** (in Summary!B2, fill down):
=SUMIFS(Orders!$D$2:$D$500, Orders!$B$2:$B$500, $A2, Orders!$C$2:$C$500, "Shipped", Orders!$A$2:$A$500, ">="&TODAY()-30)

**How it works**
- SUMIFS adds column D only for rows meeting every condition.
- $A2 matches the region on each summary row; the $ locks the column when you fill down.
- ">="&TODAY()-30 builds a date criterion as text, which SUMIFS reads correctly.

**Worked check**
- Order 12-Mar, East, Shipped, 120, today 20-Mar: counted for East.
- Order 01-Feb, East, Shipped, 80: excluded, older than 30 days.
- Order 18-Mar, East, Pending, 60: excluded, wrong status.

**Edge cases**
- Dates stored as text are silently ignored. Check with =ISNUMBER(Orders!A2).
- Blank amounts count as 0. Trailing spaces in "Shipped " will not match.
- A region with no matches returns 0, not an error.

Question: should the 30 days include today? If you want a fixed window instead, tell me the dates.

Notes

Giving real column letters and example rows lets the model produce a paste-ready formula and a checkable worked example. Always run the worked check against your own data.