Getting started

Stop a credit card payment counting as an expense twice in a sheet

Fix a credit card payment double counted as an expense: label both sides as a transfer, exclude them with SUMIFS and test that transfers net to zero in dollars.

Editorial illustration for Stop a credit card payment counting as an expense twice in a sheet

A credit card payment gets double counted because the purchases are already expenses, and then the payment from checking to the card is counted again. Label both sides of the payment as Transfer and exclude that category from spending totals, then confirm that the transfers net to zero.

Why does the double count happen?

Think of the card as a loan account. Swiping adds an expense and increases what you owe. Paying the bill reduces what you owe and reduces your checking balance, but it is not new consumption. If both the purchases and the payment are in your sheet as expenses, your month looks bigger than it was.

A worked example in dollars

Say your checking account paid $600 to the card on 12 October. In your merged sheet, spending is negative and money in is positive.

DateAccountDescriptionAmount ($)Category
Oct 03CardGrocery market-85.00Groceries
Oct 07CardGas station-45.00Transport
Oct 09CardRestaurant-120.00Dining
Oct 12CheckingCard payment-600.00Transfer
Oct 12CardPayment received600.00Transfer

Real spending in these rows is 85 + 45 + 120 = $250. Without the Transfer label, a spending total of all negative amounts would read 85 + 45 + 120 + 600 = $850. That is $600 too much.

Which formulas give the right totals?

Assume amounts are in D2:D500 and categories in E2:E500.

  • Spending excluding transfers: =-SUMIFS($D$2:$D$500, $D$2:$D$500, "<0", $E$2:$E$500, "<>Transfer"). In the example it returns 250.
  • Transfers net check: =SUMIFS($D$2:$D$500, $E$2:$E$500, "Transfer"). It should return 0, since -600 and +600 cancel.
  • Spending by category: =-SUMIFS($D$2:$D$500, $E$2:$E$500, "Dining", $D$2:$D$500, "<0").

The leading minus sign turns negative spending into a positive figure. SUMIFS takes the sum range first and then each criteria range and criteria, in pairs, and works in Excel and Google Sheets.

What if the transfer check is not zero?

  1. List the rows with the Transfer category and sort by amount.
  2. Look for an unpaired row: a payment that left checking but has no matching received row, perhaps because the card statement ends before the posting date.
  3. Check whether the two sides have different amounts, for instance a payment of $600 and a received row of $590 because a refund was netted.
  4. Import the missing statement or add the missing row, then recheck.

Can I automate the label?

Yes, partly. Use a helper column that flags likely transfers by description: =IF(OR(ISNUMBER(SEARCH("payment received", C2)), ISNUMBER(SEARCH("card payment", C2))), "Transfer", ""). Adjust the phrases to match how your bank words them. Review the result, since a similar phrase can appear in a genuine purchase.

How do I check the whole month at once?

Build a small summary beside the data. In one cell put total spending using the exclusion formula, in the next the total of all positive amounts that are not transfers, such as refunds or paychecks, and in a third the transfer check. Three numbers, side by side, tell you in seconds whether the month is clean. If the first is far larger than your card statements suggest, look at the transfers before anything else. Refunds deserve a glance too: a refund is a positive amount in a spending category, so it should reduce that category, and it should never be labelled Transfer just because it arrives on the card.

A closing checklist

  • Every payment between your own accounts labelled Transfer on both sides.
  • Spending formula excludes Transfer.
  • Transfer check equals zero.
  • Total spending compared with the card statement's new charges.

This describes the method only, in dollars. SpendixAI is built around India and INR, so it is not the tool for a dollar card.

SPENDIXAI JOURNAL

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

Explore your reports