Spreadsheets

Sum only debits from a single amount column in Excel

Sum only the debits from one signed amount column in Excel with SUMIF less than zero, total credits separately, and show spend as a positive figure.

Editorial illustration for Sum only debits from a single amount column in Excel

If your bank statement gives one signed amount column with money out as negative, total the debits with =-SUMIF($C$2:$C$500,"<0") and the credits with =SUMIF($C$2:$C$500,">0"). The minus sign in front of the first formula flips the negative total into a positive spend figure. Check the sign convention on your own statement first, because it is not the same everywhere.

How do I know which sign means a debit?

Look at one row you recognise. A salary credit and a rent payment are easy to spot. If the rent shows as negative, debits are negative, and the formulas above work as written. Some statements show spends as positive and refunds or payments as negative, which is common on card statements. In that case swap the criteria: ">0" for spend and "<0" for payments and refunds. Say your sheet has the date in column A, narration in B and signed amount in C.

Worked example with invented figures

NarrationAmount (₹)
Rent transfer-18,000
Grocery store-1,200
Salary credit45,000
Cab ride-320
Refund from shop450

Debits: ₹18,000 + ₹1,200 + ₹320 = ₹19,520, so =-SUMIF($C$2:$C$6,"<0") returns 19520. Credits: ₹45,000 + ₹450 = ₹45,450 from =SUMIF($C$2:$C$6,">0"). The net movement is the plain =SUM($C$2:$C$6), which is ₹25,930. Debits and credits should reconcile to it: 45,450 − 19,520 = 25,930.

When should I use ABS instead of the minus sign?

=ABS(SUMIF($C$2:$C$500,"<0")) also gives a positive figure, but =-SUMIF is shorter and does the same thing here, because the total of negatives is always negative or zero. ABS is more useful on single cells. In a helper column, =IF(C2<0,ABS(C2),0) creates a debit column of positive numbers, and =IF(C2>0,C2,0) creates a credit column. Put these beside the signed column if you prefer to use SUM on whole columns.

How do I get debits for one month or one category?

  • One month: =-SUMIFS($C$2:$C$500,$C$2:$C$500,"<0",$A$2:$A$500,">="&$F$1,$A$2:$A$500,"<"&EDATE($F$1,1))
  • One category: =-SUMIFS($C$2:$C$500,$C$2:$C$500,"<0",$B$2:$B$500,"Food") if categories sit in column B
  • Narrations containing a word: =-SUMIFS($C$2:$C$500,$C$2:$C$500,"<0",$B$2:$B$500,"*swiggy*")

SUMIFS lets the same column serve as both the sum range and a criteria range, so you can total a column on a condition applied to itself.

Why do my debit totals look wrong?

  • The amounts are text. Test =ISNUMBER(C2), and convert if it says FALSE.
  • Zero-amount rows or notes sit in the column. They do no harm, but text such as Balance carried forward can.
  • A balance column was pasted into the amount column by mistake. Check the headers.
  • Brackets or a Dr suffix are used instead of a minus sign. Some exports show negatives as (450) text, which needs converting first.

Can I do this in Google Sheets?

Yes. SUMIF and SUMIFS take the same arguments, and the same "<0" and ">0" criteria. To get a debit column for the whole range at once, use =ARRAYFORMULA(IF(C2:C<0,-C2:C,"")).

Once the signed column works, a small monthly table is worth building. Put the first day of each month down column G, use the SUMIFS debit formula in H and the credit formula in I, and take the difference in J. You then see money in, money out and what was left over, month by month, from one statement column. If the leftover is regularly negative, that is worth knowing before the balance tells you. Remember that a credit such as a refund counts as money in, so note it if you want pure salary only.

When a sheet stops being enough

If you are splitting statements into debits and credits by hand each month, SpendixAI lets you pick the account before importing a bank or card statement and keeps each transaction against that account, so your records stay separated by account without sign tricks.

Final checklist

  • Debit sign confirmed on a known transaction.
  • Debits plus credits equal the net SUM.
  • Card payments excluded from spend totals.
SPENDIXAI JOURNAL

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

Explore your reports