Calculation Code for Finance Sheet Required

[charllie]

Hi Folks,

I am working on creating a worksheet for my personal finances and stuck trying to do the following task (see below).

I have tried conditional formatting and if statements within the cells with no success, i am not to clevere on if statements!!

This is what i am trying to do:

Firstly:

Column A represents Payee
Column B represents Payment Date
Column C represents Amount to pay per month
Column D to O represents each month January to December etc

The rows start from row 3 to 15

Row 17 represents the total for each month so puts a total in the cell underneath each column.


What i want to be able to do is when i enter the word PAID into a particular month in any row i would like to add the amount as running total.

For example:

If i enter PAID in row 3 Column D (January) then i what the calculation to take the monthly payment figure in row 3 column C and add it as a running total in row 17 column D.

This means that i wil have a running total in row 17 column D of everything i paid in January.

I hope that all makes sense.

I would really appreciate some help and guidance with this.

Thanks

 

[mordrid]

Hi Charlie, if I have read what you want correctly then this should work

=SUMIF($D$3:$D$15,"Paid",$C$3:$C$15)

Put this in D17 and then it will add any number in row C where the corresponding row D says paid

Richard

 

[charllie]

Hi Richard,

Thanks for that, it works a treat, does exactly what i wanted, i really appreiate your time and help.

One more question if you don't mind.

I tried to use your code to perfom another function but couldn't get it to work.

I want to place a running total on the end of row 3 in column P. So i am now totalling up the costs for the row when the word Paid is in each month.

This is code i tried placing in cell P3.

=SUMIF($D$3:$O$3,"Paid",$C$3)

Thanks again for your help.

 

[mordrid]

Hi Charlie, sorry for the delay in responding but I was out all day at a BBQ.

The following formula will put the value of C3 in P3 if any cell between D3 and O3 has paid in it

{=IF(OR(D3:O3="paid"),C3,"")}

rember the {} are added by entering the formula using the ctrl shift enter key combination

Regards
Richard