Wallet and payment-gateway exports list every attempt, including payments that failed, so adding up the amount column overstates your spending. The fix is to filter on the status column, keep only the successful rows, and treat refunds as their own type. In Excel or Google Sheets, SUMIFS does this in one formula.
Why do failed payments appear at all?
An export is a log of attempts. A payment that bounced for low balance, timed out or was declined by the bank is still a logged event, with a status that says so. The amount is real but the money never moved. If you add the whole column, you count money you still have.
What columns should you look for?
- Status, which might say success, failed, pending or reversed.
- Type, which might distinguish payment, refund, top-up or cashback.
- A transaction or reference number.
- Date and time.
- Amount.
The exact words differ between wallets and gateways, so open your own export and list the distinct values in the Status column before you write any formula.
Worked example in a spreadsheet
Say the sheet has headers in row 1, statuses in column C, amounts in column D, and eight data rows in rows 2 to 9. Amounts and statuses: ₹500 success, ₹1,200 success, ₹350 failed, ₹899 success, ₹2,000 failed, ₹150 success, ₹640 success, ₹420 success.
| Question | Formula | Result |
|---|---|---|
| Money that moved | =SUMIFS($D$2:$D$9,$C$2:$C$9,"Success") | ₹3,809 |
| Failed attempts | =SUMIFS($D$2:$D$9,$C$2:$C$9,"Failed") | ₹2,350 |
| Count of successes | =COUNTIFS($C$2:$C$9,"Success") | 6 |
| Everything, wrongly | =SUM($D$2:$D$9) | ₹6,159 |
Check by hand: 500 + 1,200 + 899 + 150 + 640 + 420 = 3,809, and 350 + 2,000 = 2,350, which together make 6,159. Using the whole column would have added ₹2,350 of spending that never happened. The formulas work the same in Excel and Google Sheets.
How do you handle refunds and cashback?
- Filter the Type column to see which rows are refunds, cashback or top-ups.
- Treat a refund as a credit that reduces the original spend, not as new income.
- Treat a top-up as a transfer between your own accounts, not spending.
- Treat cashback separately, so it does not hide your real spending.
If statuses include Pending, leave them out until they settle, then re-check on the next export.
What can go wrong with the formulas?
The usual trap is a stray space or a different spelling. A status of 'Success ' with a trailing space will not match 'Success', so the SUMIFS returns zero and you suspect the data instead of the cell. Clean the column first with =TRIM(C2) in a helper column, or retype one status to test. Another trap is amounts stored as text, which SUMIFS skips; if a column total looks too small, click a cell and check whether it is left-aligned. In Google Sheets, a locale set to another country can also change how decimals read.
What if there are hundreds of rows?
Use a filter: select the header row, turn on Filter, and tick only the statuses you want. Copy the visible rows to a new sheet and total that sheet. Keep the original export untouched, so you can always go back.
When you import into a tracker
When you bring a file into SpendixAI, you review the rows before saving, which is your chance to leave out failed attempts. The product facts do not promise that it reads a Status or Type column for you, so look at the review screen rather than assume. Possible duplicate imports are flagged, and the Possible duplicate charges report lists the same amount to the same merchant within two days, which often catches a payment retried after a failure.
Whichever tool you use, keep the rule the same: only money that actually left the account is an expense.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


