Use Gemini in Sheets to build and check a small table
An issue of three items is −3, not a messy number to tidy into 3. And a total that looked right last week can quietly ignore the two rows someone added on Friday.
What you'll make
a total for a small movements table that keeps signs, treats zero as a real value, shows “Incomplete” while any value is missing, and still counts rows added later.
What you'll need
Google Sheets with Gemini (it needs an eligible Google Workspace or Google AI plan), a copy of a small table you’re allowed to use, and about fifteen minutes. Work on a copy, not the live sheet.
Example result.
Formula in D2: =IF(COUNTA(A2:A)=COUNT(B2:B),SUM(B2:B),"Incomplete")
6 movements, 6 numbers: total 4
One Change cell cleared: Incomplete
Issued rows still show −3 and −41. Write down what the values mean
Before asking for anything, note three things about your column: what a negative means (here, items issued), that zero is a recorded value, and that a blank means the number is missing. Also note whether rows get added over time. Most tables do.
Why this works: a formula can only follow rules you’ve stated; “blank is missing” and “the table grows” are the two it most often gets wrong.
What you should see: three or four short rules next to your table.
2. Ask Gemini for a guarded total
Open the sheet. At the top right, click Ask Gemini, then type in the prompt box at the bottom of the side panel and press Enter:
For the table on this tab, column A is Movement and column B is Change in units. Propose a formula for one cell that shows the total of column B only when every row with a movement in column A has a number in column B; otherwise show "Incomplete". Zero is a valid number. Keep negative values negative. It must still work when new rows are added below. Explain the formula and its ranges before I insert it. Don't change any existing cells.When the formula looks right, click the empty cell where you want it, then click Insert.
Why this works: naming the growth rule up front stops a neat fixed range such as B2:B5 that will be wrong next week.
What you should see: a formula with an explanation in the side panel, then the formula in your chosen cell.
3. Read the formula before trusting the number
Click the cell and read the formula bar. Check that the ranges run to the end of the columns (for example A2:A and B2:B), not to a fixed last row. COUNT counts numbers, including zero; COUNTA counts filled cells, so comparing the two catches a missing value; SUM keeps the signs. Work out the total yourself and compare.
Why this works: the number in the cell can look right today for the wrong reason; the formula tells you whether it will be right tomorrow.
What you should see: open-ended ranges and a total that matches your own sum.
4. Test it with a blank and a new row
Clear one value in column B: the result should change to “Incomplete”. Undo. Then add a new movement row at the bottom with a number: the total should change to include it. If Gemini changed something you didn’t want, click Undo in the side panel.
Why this works: two quick edits test the two rules that matter, on a copy, in under a minute.
What you should see: “Incomplete” with a blank, and an updated total with the new row.
Check the result
- The total matches your own sum, with issued items still negative.
- Clearing any value gives “Incomplete”, but a zero doesn’t.
- A new row at the bottom changes the total.
- No existing value changed.
The usual failure: a fixed range. The first formula often guards exactly the rows that exist today, =IF(COUNT(B2:B5)=4,SUM(B2:B5),"Incomplete"), and keeps showing last week’s total after rows are added. Spot it with the new-row test, or by reading the range. Then ask:
The formula in [cell] uses [current range] and ignores rows added below it. Rewrite it so it covers every row with a movement in column A, still shows "Incomplete" when any of those rows has no number, treats zero as valid and keeps negatives. Explain the change before I insert it.Fill in the cell and the range it currently uses.
Worked example: two rows on Friday
Fictional example. A copy of the paper-stock sheet for week 41:
Movement,Change in units
Received,8
Issued,-3
Counted adjustment,0
Returned to supplier,-2Gemini’s first formula was =IF(COUNT(B2:B5)=4,SUM(B2:B5),"Incomplete"), showing 3 (8 − 3 + 0 − 2). On Friday two rows were added, Received 5 and Issued −4, and the cell still showed 3.
- The failure. The guard counted exactly four cells, B2 to B5. The two new rows were outside it, so the total looked complete and was wrong by one.
- The repair. The correction prompt gave the formula in the example result. With all six rows it shows 4 (3 + 5 − 4), and clearing any one value shows “Incomplete”.
- What stayed right. Both issued rows stayed negative, and the counted adjustment of 0 still counts as a recorded value, so it doesn’t trigger “Incomplete”.
Task kit for this job: Download (ZIP) · Reusable instructions
Go deeper
Charts made by Gemini don’t update
If you ask Gemini for a chart and insert it, Google says it’s added to a new tab with a copy of its data, and it doesn’t respond to changes in the original. After you add rows, make the chart again or point a normal chart at the live range.
Excel files and bigger tables
Gemini in Sheets works best with native Google Sheets files; for an .xlsx, use File › Save as Google Sheets first. As the table grows, also look for numbers stored as text, which COUNT won’t count. For testing formulas more widely, see Get ChatGPT to write your spreadsheet formula.