A monthly sales total, checked one formula at a time

Daily workEN2026-10-26

This walkthrough starts with a small Excel task: total paid sales for one region during one month. Use AI to draft the formula and explain its assumptions, then check the result against a few rows you can calculate yourself. Keep the workbook, not the model’s explanation, as the final test.

§ Step 1: Describe the sheet before asking for a formula

Work on a copy of your sheet. For this example, use four columns: A contains order dates, B contains regions, C contains payment statuses, and D contains sales amounts. Headers occupy row 1; records occupy rows 2 through 500.

Set aside three input cells: F2 for the region, G2 for the first day of the reporting month, and H2 for the first day of the following month. For an October 2026 example, enter =DATE(2026,10,1) in G2 and =DATE(2026,11,1) in H2. DATE constructs an Excel date from separate year, month, and day arguments. ([support.microsoft.com](https://support.microsoft.com/en-gb/excel/functions/date-function?appver=zxl190&helpid=xlmain11.chm60113&ns=excel&syslcid=2057&uilcid=2057&version=19&utm_source=openai))

Before continuing, decide whether “sales” means gross revenue, net revenue, or cash collected. In this example, it means the signed amounts in column D for records whose status is Paid. Do not ask the model to invent that business rule.

§ Step 2: Prepare a small AI request

If you already have an OpenAI-compatible client, Vana API provides an OpenAI-style connection using its API address with /v1 appended. Gemini 3.1 Pro is one available model for this workflow. Create a Vana API account if you want to follow along through that connection. Keep your API key in the client’s credential settings, outside the spreadsheet. ([vanaapi.com](https://vanaapi.com/))

Send the column descriptions and a few invented examples rather than customer records. For the first request, ask for a draft only; do not give a connected tool permission to modify the workbook.

§ Step 3: Ask for one formula and its assumptions

Use a prompt with explicit references:

Prompt: “Write an Excel formula for J2. Dates are in A2:A500, regions in B2:B500, statuses in C2:C500, and numeric sales amounts in D2:D500. Match the region in F2 and the status Paid. Include dates on or after G2 and before H2. Preserve negative amounts. Return one formula, then explain each condition and list assumptions. Do not add error suppression.”

For these requirements, SUMIFS is a suitable function: it adds values whose corresponding rows meet multiple criteria. Its first argument is the range to sum, followed by pairs of criteria ranges and conditions. ([support.microsoft.com](https://support.microsoft.com/en-gb/excel/functions/sumifs-function?nochrome=true&utm_source=openai))

§ Step 4: Read the formula before pasting

A formula matching that request is:

=SUMIFS($D$2:$D$500,$B$2:$B$500,$F$2,$C$2:$C$500,"Paid",$A$2:$A$500,">="&$G$2,$A$2:$A$500,"<"&$H$2)

Read it against the request: sum D, match region B, match status C, then apply both date conditions to A. Every source range starts at row 2 and ends at row 500. SUMIFS requires its criteria ranges to have the same dimensions as its sum range. ([support.microsoft.com](https://support.microsoft.com/en-gb/excel/functions/sumifs-function?nochrome=true&utm_source=openai))

The date conditions describe a start-inclusive, end-exclusive interval. With the proposed inputs, that means October 1 through the moment before November 1. Check that this is the reporting boundary you intended before accepting the formula. ([support.microsoft.com](https://support.microsoft.com/en-gb/excel/functions/sumifs-function?nochrome=true&utm_source=openai))

§ Step 5: Test a result you already know

In your test copy, enter these records and leave other source rows empty:

October 1, West, Paid, 100.
October 15, West, Paid, 250.
October 20, West, Unpaid, 900.
October 22, East, Paid, 400.
November 1, West, Paid, 700.

With F2 set to West, the expected total is 350. Next, add an October 25 West Paid record for -50; the expected result becomes 300. These checks exercise status, region, date boundaries, and signed amounts separately.

§ Step 6: Debug narrowly, then save the explanation

If your result differs, send the model the exact formula, expected total, actual total, and anonymized test rows. Ask it to identify the smallest mismatch rather than rewrite everything. Inspect source values before changing the formula.

Once the checks pass, save a short note beside the output describing the inputs, business rule, and tested cases. Record that the formula covers rows 2 through 500, and revisit that boundary when the dataset grows. The useful outcome is a formula you can explain and maintain, not merely one that returns a number.