SUMIFS date range criteria: have AI write it, then verify it

"March, East region, returns excluded." Say that to an AI and a formula comes back in seconds. Knowing whether it is right is the hard part, so test it on a sheet with rows planted on the boundary days.

The sheet

A made-up sales sheet, four columns, 60 rows: Date, Region, Type, Amount. Returns carry a positive amount; only the Type column tells them apart. Dates run from late February to early April. East has one Sale and one Return on 3/1 and on 3/31, plus one Sale each on 2/28 and 4/1.

Criteria live in cells: F2 start date 2026-03-01, F3 end date 2026-03-31, F4 region East, and the formula goes in F6. Adding up the 9 March East sales by hand gives 3,810,000. That is the number the formula has to hit.

A short request

Excel formula: total sales for March, East region, excluding returns.

Both runs assumed the layout "A Date, B Region, C Type, D Amount" and returned the same formula.

=SUMIFS(D:D, A:A, ">="&DATE(2026,3,1), A:A, "<"&DATE(2026,4,1), B:B, "East", C:C, "<>Return")

The date range is half-open (on or after 3/1, before 4/1), so 3/31 rows are in, and by hand this sheet should give the same 3,810,000. That exact formula was not run through the calculation engine. The weak spot is reuse: the dates and the region are typed into the formula, and one run only described a cell-based version in words. Both said the layout was an assumption, but if your columns are in a different order, someone who pastes the formula and skips the note sums the wrong column. So name the columns and cells.

Naming columns and cells

Separating input from instructions with tags is what OpenAI's prompt engineering guide recommends. Microsoft's Copilot formula page says the same about naming the columns to analyze.

Write an Excel formula for the sheet described in <sheet>. Use only the cells and columns listed there. <sheet>Data is in A1:D61. Row 1 is the header; rows 2-61 are data.A: Date (real Excel dates, e.g. 2026-03-01)B: Region (East, West, North, South)C: Type (Sale or Return; the amount in D is positive for both)D: Amount (number)F2: start date (2026-03-01)F3: end date (2026-03-31)F4: region (East)The formula goes in F6.</sheet> Task: total of Amount where Date is from F2 to F3 inclusive, Region equals F4, and Type is not Return. Rules:- Use SUMIFS. Compare dates through the cells F2 and F3, not typed-in dates.- Use absolute references ($A$2:$A$61 style); every range must have the same size.- Output in this order: (1) the formula in one code block, (2) one line per criterion saying which cell it uses and whether the boundary day is included, (3) two cases where this formula would return 0 or a wrong number.- No other text.

"Compare through cells" lets you change the period without touching the formula. "Same size" prevents the #VALUE! in step 4. Asking about boundary days tells you where to look when you verify.

Result

Both runs returned the same formula.

=SUMIFS($D$2:$D$61,$A$2:$A$61,">="&$F$2,$A$2:$A$61,"<="&$F$3,$B$2:$B$61,$F$4,$C$2:$C$61,"<>Return")

The criterion notes said both the start and end day are included, as long as the date has no time part. The "returns 0 or a wrong number" section listed dates stored as text, dates with a time part such as 2026-03-31 14:00, and Region or Type values with stray spaces or variants such as "East " and "Returns".

Verify

[1] Paste the formula into F6 and check for 3,810,000. Putting the same sheet and formula through a calculation engine (Python formulas 1.3.4) gave 3810000.

[2] Boundary days. Change the end criterion to "<"&$F$3: the 3/31 sale of 380,000 drops out and the total should be 3,430,000. The engine agreed. Do the same at the start with ">"&$F$2: 3/1 sale of 250,000 drops, giving 3,560,000.

[3] Check with a different method. Turn on AutoFilter, set Date to 3/1–3/31, Region to East, untick Return under Type, then put =SUBTOTAL(9,D2:D61) in an empty cell. Function number 9 skips rows hidden by the filter. It should equal F6. This step was not run here (no Excel); the hand-added value stood in.

[4] Break it on purpose. Same sheet, run through the engine:

">=F2" compares against the text "F2", not the cell, so nothing matches. Excel also requires the criteria ranges to have the same number of rows and columns as the sum range. A date with a time part is the nasty one: no error, the total is just 380,000 short. If switching the date cells to General format shows decimals, there is a time part.

Where it can still go wrong

• A sheet that stores returns as negative amounts needs a different formula. "<>Return" also adds rows where Type is blank.
• Dates stored as text (left-aligned) were not tested: the engine reads text dates as numbers, so its result could differ from Excel. If you get 0, check this first.
• Do not book payment or settlement figures on the AI's formula alone; finish the checks above first.