# I would appreciate help on a formula that I should be able to figure out but can't

#### e_a_g_l_e_p_i

##### New Member
I have a worksheet that I track all my credit card activity. What I need is a formula that gives me a total in 3 places

option 1 In column B each entry is either debit or credit

option 2 In column D there are dollar amounts that are either (dollar amount) or dollar amount

Above is what I have to work with I am sure it can be done very easy in options 1 or 2

Here is what I need.

In column E I have a title "total charged"

In column F I have "total Payments"

In column G I have "Running Total"

In the easiest way I would like to keep a running total as I copy and paste data from my bank. So for each cell that there is either a debit in Column B I need that to appear in column E "Total charged".

I need the same in Column F for "total payments"

For each column E,F,G I want the whole column all the way down to be calculated as I add data.

So I will end up with 3 formulas that I can place in each column so I have a running total.

It doesn't matter to me if you use the negative values for charges and the positive values for payments or if you chose to use column B and use debit or credit.

The most important part is I want the formula to be active in all cells in each column E,F and G so as I add new data each month the cells I have marked "Total charged", Total Payments" and "Running Totals"

Now I know I have given a lot of info but I wanted you to have enough to figure out what I want to do. I will stop here and if anyone has questions I will be happy to answer them.

Thanks

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
option 1 In column B each entry is either debit or credit - so thats the words

In column E I have a title "total charged"

in column E for the charges these would all be negative values so
=SUMIF(\$B\$2:B2,"debit",\$Column_with_Amount\$Row)
copy down and
that should give a running total

In column F I have "total Payments"

I assume this is just the credits

=SUMIF(\$B\$2:B2,"credit",\$Column_with_Amount\$Row:ColunROW)
and copy down

In column G I have "Running Total"

This would need to subtract one from the other - I assume the debits are not entered as negative

=SUMIF(\$B\$2:B2,"credit",\$Column_with_Amount\$Row:ColunROW) - SUMIF(\$B\$2:B2,"debit",\$Column_with_Amount\$Row:ColunROW)

assuming starting in row2 and the amount is in column D

IN E2
=SUMIF(\$B\$2:B2,"debit",\$D\$2:D2)

IN F2
=SUMIF(\$B\$2:B2,"credit",\$D\$2:D2)

IN G2
Is the red just a formatin or is the actual value in RED negative, if negative than use + between the two formulas
=SUMIF(\$B\$2:B2,"credit",\$D\$2:D2) - SUMIF(\$B\$2:B2,"debit",\$D\$2:D2)

and copy all three down the sheet
we can put an IF to not sure any value if not entry using an IF(D2="", "", formula)

Last edited:
option 1 In column B each entry is either debit or credit - so thats the words

In column E I have a title "total charged"

in column E for the charges these would all be negative values so
=SUMIF(\$B\$2:B2,"debit",\$Column_with_Amount\$Row)
copy down and
that should give a running total

In column F I have "total Payments"

I assume this is just the credits

=SUMIF(\$B\$2:B2,"credit",\$Column_with_Amount\$Row:ColunROW)
and copy down

In column G I have "Running Total"

This would need to subtract one from the other - I assume the debits are not entered as negative

=SUMIF(\$B\$2:B2,"credit",\$Column_with_Amount\$Row:ColunROW) - SUMIF(\$B\$2:B2,"debit",\$Column_with_Amount\$Row:ColunROW)

assuming starting in row2 and the amount is in column D

IN E2
=SUMIF(\$B\$2:B2,"debit",\$D\$2:D2)

IN F2
=SUMIF(\$B\$2:B2,"credit",\$D\$2:D2)

IN G2
Is the red just a formatin or is the actual value in RED negative, if negative than use + between the two formulas
=SUMIF(\$B\$2:B2,"credit",\$D\$2:D2) - SUMIF(\$B\$2:B2,"debit",\$D\$2:D2)

and copy all three down the sheet
we can put an IF to not sure any value if not entry using an IF(D2="", "", formula)

Thanks for your reply but I was afraid that I confused everyone with all the info I gave. Let me try again
I just realized that Column B which has credit and debit won't work because there are not \$ amounts in those cells so all the data will have to come from column D.

In column D there is "Amount" and they are charges and credits in one column. Charges look like this (\$16.78) Payments are like this \$16.78

Like I said I have E1 Total Charged" F1 "Total Payments" then in G1 I wanted a running total in which E1 & F1 would be calculated and the sum would be in G1.

The reason I say the formula has to look at the entire column D is because those are the columns that will have the data. In dollar amounts positive and negative.

Here is the formula I want for each of my total columns I just don't know how to write them.

In column E1
I want a formula that gives me the all the amounts from the whole column D that have red (\$\$\$)'s around the dollar amount, meaning it was a charge.

In column F1

I want a formula that gives me a total for all payments that are in column D and show up like this \$16.78

In column G1

I want a calculation of column E minus column F

So in summary E1,F1,G1 will always be updated from data in the whole column D

Does this make more sense??

I just realized that Column B which has credit and debit won't work because there are not \$ amounts in those cells so all the data will have to come from column D.
thats what I used - Column D for the amounts and then used the text/Word in column B - Credit or Debit to do the total
In column D there is "Amount" and they are charges and credits in one column. Charges look like this (\$16.78) Payments are like this \$16.78
and for the (\$16.78) would column B have debit ?

In column E1
I want a formula that gives me the all the amounts from the whole column D that have red (\$\$\$)'s around the dollar amount, meaning it was a charge.
and for RED amounts would the word DEBIT appear in column B - if so , then I think thats what I supplied

IN E2
=SUMIF(\$B\$2:B2,"debit",\$D\$2:D2)

so i think I did what you asked for -

if that does not work - then in
E2 we could just use D values and sum where less than zero

=SUMIF(\$D\$2:D2,"<0")
and copy down the sheet to show a running total
=IF(D2="","", SUMIF(\$D\$2:D2,"<0"))
which will leave the blank cells waiting for you to copy data into - blank

and for F
=IF(D2="","", SUMIF(\$D\$2:D2,">0"))

and for G
=IF(D2="","", SUMIF(\$D\$2:D2,">0"))+IF(D2="","", SUMIF(\$D\$2:D2,"<0"))

i used + because the result of sum of RED values should be negative

thats what I used - Column D for the amounts and then used the text/Word in column B - Credit or Debit to do the total
and for the (\$16.78) would column B have debit ?

and for RED amounts would the word DEBIT appear in column B - if so , then I think thats what I supplied

IN E2
=SUMIF(\$B\$2:B2,"debit",\$D\$2:D2)

so i think I did what you asked for -

if that does not work - then in
E2 we could just use D values and sum where less than zero

=SUMIF(\$D\$2:D2,"<0")
and copy down the sheet to show a running total
=IF(D2="","", SUMIF(\$D\$2:D2,"<0"))
which will leave the blank cells waiting for you to copy data into - blank

and for F
=IF(D2="","", SUMIF(\$D\$2:D2,">0"))

and for G
=IF(D2="","", SUMIF(\$D\$2:D2,">0"))+IF(D2="","", SUMIF(\$D\$2:D2,"<0"))

i used + because the result of sum of RED values should be negative

Thank you so much for your help

does that work all OK then ?

Replies
4
Views
294
Replies
6
Views
563
Replies
4
Views
150
Replies
1
Views
207
Replies
4
Views
218

### Forum statistics

1,203,486
Messages
6,055,709
Members
444,809
Latest member
mwh85 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back