Spreadsheets

Own-account transfers in an expense sheet: avoid double counting

Stop own-account transfers and credit card bill payments inflating your spending: add a Type column and use SUMIFS that ignore transfers, with a worked example.

Editorial illustration for Own-account transfers in an expense sheet: avoid double counting

Add a Type column with three values (Expense, Income, Transfer) and make every spending total use only rows whose Type is Expense. A credit card bill payment, a wallet top-up or a move from savings to current account is a Transfer. If you count both the card purchases and the bill payment as expenses, you count the same money twice.

How does a card bill inflate your spending?

Say you spend ₹32,000 on a credit card during the month: groceries, fuel, a phone recharge. You record each as an expense, which is right. When the bill arrives you pay ₹32,000 from your savings account, and that debit lands on your savings statement. If your sheet also records it as an expense, your month shows 32,000 + 32,000 = ₹64,000 of spending. The real figure is ₹32,000.

EntryAccountAmountType
Groceries on cardCredit card₹6,400Expense
Fuel on cardCredit card₹3,600Expense
Other card purchasesCredit card₹22,000Expense
Card bill paymentSavings account₹32,000Transfer

The three card purchases add up to 6,400 + 3,600 + 22,000 = ₹32,000. The bill payment is the same ₹32,000 moving from savings to settle the card, so it is not new spending.

What formulas ignore transfers?

  • Total spending: =SUMIFS($D$2:$D$800,$E$2:$E$800,"Expense")
  • Spending by category: =SUMIFS($D$2:$D$800,$C$2:$C$800,"Groceries",$E$2:$E$800,"Expense")
  • Total income: =SUMIFS($D$2:$D$800,$E$2:$E$800,"Income")
  • Check for untyped rows: =COUNTIF($E$2:$E$800,"")

Here C is Category, D is Amount and E is Type. The last one should read zero, and it catches the rows that would otherwise slip into totals unlabelled.

Which entries count as transfers?

  • Paying a credit card bill from a bank account.
  • Moving money between your own savings and current accounts.
  • Topping up a wallet from your bank.
  • Withdrawing cash from an ATM, which moves money from the bank to your pocket.
  • Moving money to a fixed deposit in your own name, if you think of it as saving rather than spending. How you treat it is your call.

What is not a transfer: interest on a card, a late fee, a joining fee and the finance charge on an EMI. These are real costs, so record them as expenses. A refund to a card is not a transfer either.

How do you check that transfers balance?

Each transfer has two sides: money leaves one account and arrives in another, or is applied to a card. Add From and To columns, and every transfer row is entered once or twice, depending on how you keep records. A simple check is that the total sent out equals the total received in for the period: =SUMIFS($D$2:$D$800,$E$2:$E$800,"Transfer",$G$2:$G$800,"Out")-SUMIFS($D$2:$D$800,$E$2:$E$800,"Transfer",$G$2:$G$800,"In"), where G holds the direction. The answer should be zero, unless a transfer is still in transit at month end.

What happens when you import statements?

Statements show the bank side of each transfer as a plain debit, with no label saying it is a card payment. When you import both your bank and card statements into an app, the card bill payment on the bank statement and the purchases on the card statement can both appear. In SpendixAI, rows are shown for review before they are saved, and each transaction keeps the account you picked. Give the card bill payment a custom category you name Card bill payment, and check your reports and filters so it does not sit inside your spending view. Test this on one month first, since how you set up the category decides the totals.

A transfer-proof checklist

  1. Add a Type column and fill it for every row.
  2. Make every spending total use Type Expense.
  3. Use the blank-count check each week.
  4. Compare category totals with your card and bank statements.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports