Calculation Code for Finance Sheet Required

charllie

Well-known Member
Joined
Apr 6, 2005
Messages
983
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

mordrid

Board Regular
Joined
Jul 22, 2005
Messages
234
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

Well-known Member
Joined
Apr 6, 2005
Messages
983
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.

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

Thanks again for your help.
 

mordrid

Board Regular
Joined
Jul 22, 2005
Messages
234
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
 

Forum statistics

Threads
1,181,102
Messages
5,928,062
Members
436,586
Latest member
latintxn

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top