Audit a Suggested Formula Before Filling It Down

Today's Goals

1. Distinguish row count, seat count, and recorded receipts.
2. Check a proposed SUMIF or SUMIFS formula against fixed rows.
3. Document range locks, blanks, and a counterexample before filling down.

Plain-Language Walkthrough

A formula can be valid and answer the wrong question

The D1 import audit produced a clean eight-row source and a ten-seat baseline. Now the desk wants confirmed seats for each event. A familiar spreadsheet trap appears immediately: COUNTIFS can count rows whose Event and Status match, and its syntax may be perfectly valid. Yet the question asks for seats, not bookings. Repair Cafe has three confirmed booking rows, A02, A05, and A08, but they reserve one, two, and one seats. The answer is four seats. The difference is not a rounding problem. It is a mismatch between the unit in the question and the unit returned by the formula.

Use a conditional sum over the Seats column. Name the imported source tab Registrations. In the sample import, the columns are EntryID, Event, Channel, Seats, PaidUSD, Status; the eight data rows occupy rows 2 through 9. If A2 of a separate summary sheet holds an event name, a possible Google Sheets expression is =SUMIFS(Registrations!$D$2:$D$9,Registrations!$B$2:$B$9,A2,Registrations!$F$2:$F$9,"confirmed"). The dollar signs keep the three source ranges fixed when the summary formula moves down, while A2 becomes A3 and A4. This is a candidate to inspect, not a magic string to paste into an unrelated workbook. If your import changes column order or row boundaries, adapt the references and check them again.

Sign in and link WeChat to read

Sign in, then link your WeChat mini program account on the account page. Linked accounts share one free article per day. Unlock additional articles with rewarded videos in the mini program.

Scan in WeChat to open Dayward AI. Open the same course and chapter, choose the rewarded video, then refresh this page after completion.

Unlocked — refresh to readSign in / scan with WeChat →