For a freelancer, a payment gateway or wallet export is not just a spending record, it is evidence of income. If failed attempts, pending items and refunds sit in the same list as settled payments, you will overstate what you earned. Total only the settled rows, subtract refunds, and then match the result to what actually reached your bank.
Why overcounting is risky when income is irregular
In a quiet month, an inflated income figure feels like good news and hides a cash problem. It can also cause trouble later, when someone, perhaps your CA, asks why your records do not match the bank. A sheet that excludes unsettled rows is easier to defend.
What a typical export looks like
Names vary, so check yours, but exports commonly list a type, a status and an amount. Say five rows for a month:
| Client | Type | Status | Amount in ₹ |
|---|---|---|---|
| Client A | Payment | Success | 25,000 |
| Client B | Payment | Failed | 18,000 |
| Client B | Payment | Success | 18,000 |
| Client C | Payment | Pending | 12,500 |
| Client A | Refund | Success | 4,000 |
Adding every payment line gives 25,000 + 18,000 + 18,000 + 12,500 = ₹73,500. The honest figure is different. Settled payments are 25,000 + 18,000 = ₹43,000. Take off the refund of ₹4,000 and settled income is ₹39,000. Pending ₹12,500 is not income yet.
How do I get the settled figure in a formula?
With type in column B, status in C and amount in D:
- Settled payments: =SUMIFS($D$2:$D$500,$B$2:$B$500,"Payment",$C$2:$C$500,"Success")
- Refunds: =SUMIFS($D$2:$D$500,$B$2:$B$500,"Refund",$C$2:$C$500,"Success")
- Settled income: the first result minus the second
- Waiting to settle: =SUMIFS($D$2:$D$500,$C$2:$C$500,"Pending")
Does the sheet match my bank?
Not always to the rupee. Gateways typically pay out in batches, so the bank may show one credit for several payments, and some exports include a separate fee or deduction column. Say your settled income is ₹39,000 and the bank shows a single credit of ₹38,200. The gap is 39,000 - 38,200 = ₹800. Look for a fee column in the export, or for a payment that settled after month end. Write the explanation next to the figure so you do not have to rediscover it.
What should I record for pending items?
Add a Chase date column. A pending row with no chase date is a payment you have forgotten. When it finally settles, change its status in your sheet and the settled total will move on its own.
When the sheet starts to feel heavy, SpendixAI can bring in bank statements as PDF, CSV or Excel and show rows for review before saving, which is a useful second check against what the gateway says. It is not an invoicing or GST filing tool, and tax treatment of any receipt is a matter for your CA.
What if the gateway shows fees and taxes as separate rows?
Some exports list the amount a client paid and the deduction by the gateway as two lines or two columns. we cannot tell you what your gateway charges, so look at your own file. Keep the gross amount, the deduction and the net amount in separate columns instead of overwriting one with another, then check that gross minus deductions equals what the payout shows. Say a payment is ₹10,000, the deduction column shows ₹236 and the payout line says ₹9,764: 10,000 - 236 = 9,764, which agrees. When the maths agrees, you can show your CA a gross figure for the client and a net figure for the bank without a gap in between.
If the numbers never agree, stop adding and ask the gateway's support for a settlement report for that period, quoting the dates you are checking.
Month-end list
- Settled, refunded and pending totals each calculated
- Settled income compared with bank credits
- Gap explained in one line
- Every pending row has a chase date
Useful ideas start with a clearer picture of your own spending.
Explore your reports

