To get a working expense formula from an AI assistant, tell it the app, the exact columns and ranges, and the one result you want. Then test the formula on three rows you add by hand, where you already know the answer. Most wrong formulas come from vague prompts, and most hidden errors surface the moment you check a total you computed yourself.
Describe the sheet before the goal
Say what each column holds, where the data starts and ends, and which app you use. A good opening: "I use Excel. Column A has dates, column B has the category as text, column C has the amount in rupees. The data is in rows 2 to 500. Cell E1 holds the first day of a month."
Then state the goal: "Write one formula that adds up the amounts in column C for the month in E1. Use fixed ranges like $C$2:$C$500, not whole columns. Explain each argument in one line. Tell me whether it needs Excel 365 or also works in older versions and in Google Sheets."
Three formulas you can ask for and verify
- Monthly total: =SUMIFS($C$2:$C$500, $A$2:$A$500, ">="&E1, $A$2:$A$500, "<"&EDATE(E1,1)) adds amounts dated from the first of the month up to, but not including, the first of the next month. SUMIFS and EDATE work in Excel and in Google Sheets.
- Category share: =SUMIFS($C$2:$C$500, $B$2:$B$500, "Food")/SUM($C$2:$C$500) gives Food as a fraction of everything; format the cell as a percentage.
- Running balance: with an opening balance in F1 and credits entered as positive amounts and debits as negative, put =$F$1+SUM($C$2:C2) in D2 and fill it down. The first reference is fixed, the second grows with each row.
How do I test it on three rows?
Leave your real data alone and add three rows at the bottom, dated inside the month in E1, so you know the right answer in advance. Say E1 is 1 April and you enter: 10 April, Food, ₹100; 15 April, Travel, ₹250; 20 April, Food, ₹50.
- The monthly total should rise by exactly ₹400.
- The Food total from the formula should rise by ₹150, and the share of those three rows alone is 150 divided by 400, which is 37.5%.
- Add a fourth row dated 2 May with ₹999. The April total must not change. If it does, the date test is wrong.
- Type a category with a trailing space, like "Food ". If it is missed, your categories need cleaning before any formula will work.
What if the formula returns zero or an error?
Paste the formula, the first five rows of your data and what you see back, then ask: "Find the cause and change as little as possible." Common causes are dates stored as text, amounts stored as text with a rupee symbol typed in, and a range that stops a row too early. Ask the assistant to explain the cause rather than silently rewriting it, so you learn to spot it next time.
When a sheet stops being enough
Formulas are a good way to learn your numbers. If statements arrive every month and you spend more time cleaning rows than reading them, SpendixAI imports PDF, CSV and Excel statements, lets you review rows before saving, and categorises them in stages so a sheet does not have to.
Quick checklist
- Name the app and the exact ranges in the prompt.
- Ask for an explanation of each argument.
- Test on three rows with known answers.
- Keep the tested formula in a notes cell so you can reuse it.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


