Excel and Sheets formulas you can actually trust
Clients send their sales data as spreadsheets, and every cleanup job needs a SUMIFS, an XLOOKUP, or a pivot you half-remember how to build. Excel formula help is a whole category in Financial Cents' prompt library for bookkeepers because it works so reliably — the trick is getting the formula explained and stress-tested, not just handed to you.
You are an Excel expert who writes formulas for a bookkeeper working with client spreadsheets. I'll describe the layout and the goal; you give me the formula and make sure I understand it well enough to trust it with client numbers. Spreadsheet layout (sheet names, columns, a few sample values with any real names changed): {{spreadsheet_layout}} What I need it to do: {{goal}} Excel version or Google Sheets: {{excel_or_sheets}} Return: 1. The exact formula, ready to paste, using my actual column references. 2. A plain-English walkthrough, clause by clause. 3. The two or three ways this formula typically breaks — blank cells, numbers stored as text, dates stored as text — and how to spot each one in MY data. 4. A sanity check: a manual calculation on two or three rows I can compare against the formula's result before I rely on it. Rules: if my layout description is missing something you need (like which column holds the date), ask me instead of assuming. If my version doesn't support a function (XLOOKUP on Excel 2016), say so and give the compatible alternative. Never ask me to paste the client's actual data into this chat — work only from the layout description and altered sample values.
Fill in your details and the prompt updates live — then copy.
Formula for your summary sheet (customer in G2, month start date in H1): =SUMIFS(Sales!D:D, Sales!B:B, G2, Sales!E:E, "Open", Sales!A:A, ">="&H1, Sales!A:A, "<"&EOMONTH(H1,0)+1) Walkthrough: sum column D where the customer matches G2, status is exactly "Open", and the date falls inside H1's month... How it breaks: (1) If column A dates are text, the date filters silently return 0 — check with =ISNUMBER(A2). (2) Trailing spaces in "Open " won't match — check with =LEN(E2)... Sanity check: filter Sales to one customer + Open + March manually and compare the visible sum in the status bar.
The full workflow
- Describe the layout with column letters and fake sample values — never paste the client's rows.
- Paste the formula into a copy of the file, not the original.
- Run the suggested sanity check on a few rows before using results anywhere.
- Save formulas that survive contact with real data in a snippets doc.
Watch out for
Run the sanity check every time — a formula that's 95% right (off-by-one date boundaries are the classic) produces numbers that look plausible and are wrong.
The layout description is enough; the client's actual rows are customer financial data covered by your confidentiality obligations and the FTC Safeguards Rule. If you need AI inside the real file, that's what employer-sanctioned Copilot in Excel is for — and it belongs in your WISP.
Where this comes from
Every use case on this site is grounded in real reports from working bookkeepers — not invented by us.