With the figure in C and the flag in D, a signed amount is one formula: =IF(D2="Dr",-C2,C2). Money out turns negative, money in stays positive, and SUM then gives the net movement. SWITCH does the same job and can flag unexpected values, and a check column catches flags that are neither Dr nor Cr.
What is the simplest formula?
Assume column C holds the amount as a plain positive number and column D holds the words Dr or Cr. In E2 write:
- =IF(D2="Dr",-C2,C2)
- Excel compares text without regard to capitals, so "dr", "Dr" and "DR" all match.
- Fill down the column, then total E for the net movement.
On most bank-account statements, Dr is an amount debited, meaning money out. Test the rule on a row you remember, such as a rent payment, before filling it down the column. Credit card layouts can behave differently, so look at yours.
What if the flag has spaces or odd entries?
A trailing space turns "Dr " into something the formula does not recognise, so the amount quietly stays positive. Clean the flag inside the formula:
- =IF(TRIM(D2)="Dr",-C2,C2) copes with stray spaces.
- =SWITCH(TRIM(D2),"Dr",-C2,"Cr",C2,NA()) returns #N/A for anything unexpected, so a bad flag becomes visible rather than silently wrong.
SWITCH needs Excel 2019 or Microsoft 365 and works in Google Sheets. On an older version, nest IFs: =IF(TRIM(D2)="Dr",-C2,IF(TRIM(D2)="Cr",C2,NA())).
How do you spot rows that are neither Dr nor Cr?
Add a one-cell check somewhere clear of the data:
- =COUNTA(D2:D500)-COUNTIF(D2:D500,"Dr")-COUNTIF(D2:D500,"Cr")
- It counts all filled flag cells, then subtracts the Dr and Cr ones.
- The result should be 0. Anything above 0 is the number of rows with a stray space, a typo or a blank-looking character.
To find them, add a filter on the flag column and look for entries that appear twice in the list, such as Dr and Dr with a space.
| Amount (C) | Flag (D) | Signed amount |
|---|---|---|
| 18,000.00 | Dr | -18000 |
| 2,350.00 | DR | -2350 |
| 52,000.00 | Cr | 52000 |
| 500.00 | Dr | -500 with TRIM, +500 without it |
What does SWITCH give you over IF?
Both work. IF is shorter, and everyone understands it. SWITCH reads more clearly once you add a third value, for example a flag such as "OB" for an opening balance row. A statement with a rare label can be handled as another pair inside SWITCH rather than a deeper nest of brackets, and the default argument at the end is a natural place for the error. Pick whichever you can still read in six months.
Worked example with hypothetical amounts
Say a mini statement has Dr 18,000.00 for rent, Dr 2,350.00 for groceries, Dr 500.00 for a UPI payment and Cr 52,000.00 for salary. The signed column reads -18,000, -2,350, -500 and 52,000. Their sum is 31,150, which matches opening balance plus 31,150 equals closing balance. If your opening balance was ₹25,000, the closing balance should be ₹56,150. If it is not, one flag is wrong.
What can you do with the signed column?
- Money out in total: =-SUMIF(E2:E500,"<0")
- Money in in total: =SUMIF(E2:E500,">0")
- Net movement: =SUM(E2:E500)
- A category's net: =SUMIFS($E$2:$E$500,$B$2:$B$500,"Rent")
All four work only when column E holds numbers, which is the reason to run the flag check before you build anything on top.
If the flag column sometimes says Debit and Credit in full, or D and C, adjust the test to match what your statement prints, and let the check cell confirm that nothing was left out.
Closing checklist
- Amount column converted to numbers.
- Flag cleaned with TRIM.
- Check cell reads 0.
- Net total of the signed column equals closing balance minus opening balance.
If you would rather skip the flag handling, SpendixAI takes PDF, CSV or Excel statements and lets you review rows before saving; it is worth confirming that amounts and directions look right in that review.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

