Describe the result and get the formula

Write a request that gets a working formula for your own sheet, then check it against a total you know.

Three rows don't belong in North's March

[[doc statement | Orders: A Date, B Region, C Status, D Amount | 3 Mar, North, Paid S$1,200 | 5 Mar, North, Cancelled S$800 (1) | 12 Mar, South, Paid S$950 (2) | 2 Apr, North, Paid S$600 (3) | 1: Cancelled | 2: South, not North | 3: April | find 3]]

---

It can't **see your sheet**

[[rewrite Give me a formula for sales by region. | Google Sheets, sheet Orders. A Date, B Region, C Status, D Amount. Region in F2. Total D where B matches F2, C is Paid, dates in March 2026. Sample rows attached. | App, columns, then the result]]

---

Ask what each part checks

``` =SUMIFS(D:D, B:B, F2, C:C, "Paid", A:A, ">="&DATE(2026,3,1), A:A, "<"&DATE(2026,4,1)) ```

D:D: add up Amount B:B, F2: Region matches F2 C:C, "Paid": Status is Paid A:A, ">="&DATE(2026,3,1): from 1 March A:A, "<"&DATE(2026,4,1): before 1 April

If it says Status is in C and yours is in B, the formula checks the wrong column.

---

Test it on a total **you know**

[[big S$1,200 | North, March, Paid only, from the four rows above]]

Filter by hand once, the old way. If it matches, try a second case, like a region with a cancelled order.

---

?? Your formula says S$2,000 for North in March. The rows: Paid S$1,200 and Cancelled S$800 in March, Paid S$600 in April. What's missing? A March date range + The Status is Paid condition The right column to add up => S$1,200 + S$800 = S$2,000, so the cancelled order got in. A missing date range would give S$1,800 instead (S$1,200 + S$600).

---

[[match Which family fits each request? | Sales where region is North and status is Paid > SUMIFS | A customer's manager from another table > XLOOKUP | Text like 3 Mar 2026 made into a real date > DATEVALUE]]

Say which app you use. Excel 2016 and 2019 have no XLOOKUP.

---

?# Your request to the assistant I use {Excel or Google Sheets}. The sheet is {sheet name}, headers in row 1. Columns: {letter and header for each}. I need {the result in words}, where {each condition}. Explain each part in one line. Sample rows below, client names and IDs made up.

---

?= Build your summary The assistant needs your app, the columns and a few sample rows | It can work out your layout from the result you describe | Column letters are enough without the headers Ask for one line on what each part of the formula checks | Ask for the shortest formula and skip the explanation | One line on the whole formula is enough Test it on a total you already worked out by hand | If it returns a number without an error, it works | One test on any region is enough

Pick one total you work out by hand each week. Fill in the request above, paste it to your assistant, and check the formula against last week's figure.

Course

Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).