How to check an AI-written Excel formula on five rows

On April 16, 2026, a spreadsheet user described finding formulas that pointed at the wrong rows of an inputs tab in a model ChatGPT had helped generate. One reference they checked was in cell F3. Before I trust a formula in Excel, I’d make it prove itself on five rows I can add up by hand.

What the reports show

In a September 27, 2023 Microsoft Community post, someone using ChatGPT to help write an Excel formula expected it to tell them which fruit names appeared in a second list. The formula said yes for all three, including an apple that wasn’t there. A forum reply explained that the formula was checking whether a result was a number, and the poster confirmed a different formula worked. That older report still makes a good case for including a row you expect to fail.

For the exercise below, Microsoft’s SUMIF documentation is the reference. SUMIF adds values that meet one condition. If you give it separate condition and amount ranges, keep the rows aligned.

Try one small task

1. State the rule before asking for a formula

   Write a sentence describing what counts. For example: “Add quantities only for rows whose status is Ready.” Name the columns and where the data begins and ends. Tell the assistant which spreadsheet app you use.

   Keep this first exercise to one condition. If your actual task needs dates, returns and several statuses, build those checks after you can explain the simple version.

2. Create five rows with an answer you know

   Use the practice data below. Put Status in A1 and Quantity in B1, with the records in rows 2 through 6. The Ready quantities are 2, 3 and 4. Their total is 9.

   Five-row practice data

   Row	A: Status	B: Quantity
   2	Ready	2
   3	Hold	7
   4	Ready	3
   5	Hold	1
   6	Ready	4

   Write 9 in a separate note before entering the formula. That answer is your independent reference.

3. Compare the proposed formula with the rule

   For this practice layout, =SUMIF(A2:A6,"Ready",B2:B6) adds the quantities whose status is Ready. Check each range against the sheet. The condition range is A2:A6; the quantities are B2:B6.

   If your Excel setup uses semicolons between arguments, follow that local syntax. If the formula fails, keep the exact error message. Do not replace all your formulas with another suggestion before identifying the difference.

   Help me write one Excel formula.
   My rule: sum Quantity only where Status is Ready.
   Layout: headers in A1:B1; Status in A2:A6; numeric Quantity in B2:B6.
   Sample rows: Ready 2; Hold 7; Ready 3; Hold 1; Ready 4.
   Expected result: 9.
   Give the formula and explain its ranges.
   Then describe a matching-row test, an excluded-row test and what happens if I add row 7.
   Do not change the source values or replace my whole workbook.

   Microsoft’s guide to Excel references shows how a dollar sign keeps part of a cell reference fixed when you copy a formula.

   You can paste an assistant’s formula into a practice workbook or test one suggested inside Excel. In either case, fill the formula down one row and look for references that moved. Compare them with the cells you meant to keep fixed.

4. Change a matching and an excluded input

   First change B6 from 4 to 10. The answer should become 15. Restore 4, then change B3 from 7 to 70. The answer should stay 9 because that row is Hold.

   Then add a new Ready row outside the original range. A fixed A2:A6 range will not include it. That is a design choice you need to handle before using the formula in a list that grows. Restore the practice data before moving on.

This is the kind of check I help people practice one-to-one.

Check the result before you use it

A result of 9 is only the first check. A formula aimed at the wrong range can match once by coincidence. The two changed-input tests tell you more about which rows it actually uses.

For imported data, check the source rows against the spreadsheet before testing the formula. A working formula cannot repair missing source data.

For other practical tasks, explore the AI guidance topics.

Make this work for your own situation

The harder task is adapting the rule to your real workbook: growing tables, blank cells and records that need a different treatment. You also need to decide what result should count as correct before asking AI to rewrite the logic.

Bring one recurring calculation and a few anonymous examples. We can build test cases for the normal result and an exception, then decide which ranges need to grow with the data.

Last checked November 12, 2026. Draft prepared with AI assistance and checked against the linked sources.