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]

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

 

[etaf]

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)

 

[e_a_g_l_e_p_i]

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??

 

[etaf]

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 -

shame , We cant load a spreadsheet

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

 

[e_a_g_l_e_p_i]

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 -

shame , We cant load a spreadsheet

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

 

[etaf]

does that work all OK then ?