Formula to add amout if paid or "0" if not paid in a specific column.

fedcco

New Member
Joined
Aug 14, 2012
Messages
22
I need a formula that will add the late fee amount from column "J" to column "L" if "Paid" is typed on column "K" or $0 in column "L" if "Owed" is typed in column "K". I also need the same function to happen to column "N" if "Paid or Owed" is typed in column "M". Your input will be greatly appreciated.

Mo.Rental AmountPmt. MethPmt. Meth. CommentsDue DateDate Tenant PaidMgmt. Fees Coll'd.Repairs Amt.Repairs Chk. No.Late Fee Amt.Paid or OwedLate Fee 2 RCRPaid or OwedLate Fee 2 OwnerBal. Due OwnerDate Paid to OwnerPmt. MethRCR Chk. No.Pmt. Meth. Comments
Jan$1,200 1/1/191/4/19 $50Paid$50 $0$1,200
Feb$1,300 2/1/192/7/19 $80Owed$80 $0$1,300
Mar$1,400 3/1/193/11/19 $120 $0 $120$1,520
Apr 4/1/19 $0 $0 $0$0
<colgroup><col width="31" style="width: 23pt; mso-width-source: userset; mso-width-alt: 1133;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;"> <col width="39" style="width: 29pt; mso-width-source: userset; mso-width-alt: 1426;"> <col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;" span="2"> <col width="46" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1682;"> <col width="48" style="width: 36pt; mso-width-source: userset; mso-width-alt: 1755;"> <col width="54" style="width: 41pt; mso-width-source: userset; mso-width-alt: 1974;"> <col width="40" style="width: 30pt; mso-width-source: userset; mso-width-alt: 1462;" span="2"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;" span="2"> <col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1609;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;"> <col width="39" style="width: 29pt; mso-width-source: userset; mso-width-alt: 1426;"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;"> <tbody> </tbody>
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,658
Office Version
  1. 365
Platform
  1. Windows
How about
=IF(K2="paid",L2,0)
 

fedcco

New Member
Joined
Aug 14, 2012
Messages
22
Thanks fluff for your quick response but I need the formula to add the late fee amount from column "J" to column "L" if "Paid" is typed on column "K" or $0 in column "L" if the late fee is still "Owed".

Something like =IF(K2="paid",then L2 =J2,but if K2="owed" then,$0)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,658
Office Version
  1. 365
Platform
  1. Windows
What happens if K is neither Paid nor Owed?
 

fedcco

New Member
Joined
Aug 14, 2012
Messages
22

ADVERTISEMENT

It will always be one or the other.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,658
Office Version
  1. 365
Platform
  1. Windows
Oops just realised I got the cell wrong in the formula, it should be
=IF(K2="paid",J2,0)
 

fedcco

New Member
Joined
Aug 14, 2012
Messages
22

ADVERTISEMENT

Oops my bad, I added new rows and forgot to change your newest formula. It works fine. Thanks a Million Fluff.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,658
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

fedcco

New Member
Joined
Aug 14, 2012
Messages
22
Unfortunately, I just realized that I should have headed your question regarding "what happens if K2 is neither paid nor owed". The formula that adds the late fee, takes into consideration that if the late fee is paid prior to the 10th. of the month, then it goes to the office but if it's paid on the 10th of after, it goes to the owner. Hence the "M" and "N" columns. So I need to add the late fee to the owners column if paid on or after the 10th. This is the formula that makes this happen.

=((F16-E16)>=3)*50+((F16-E16)>=4)*(F16-E16-3)*10 (it adds $50 on the 4th. day late and $10 each additional day of the rent being late and if paid prior to the 10th. it stays in the office but if on the 10th or after, it goes to the owner.)

Mo.Rental AmountPmt. MethPmt. Meth. CommentsDue DateDate Tenant PaidMgmt. Fees Coll'd.Repairs Amt.Repairs Chk. No.Late Fee Amt.Paid or Owed RCRLate Fee 2 RCRPaid or Owed OwnerLate Fee 2 OwnerBal. Due OwnerDate Pmt. Made to OwnerPmt. MethRCR Chk. No.Pmt. Meth. Comments
Jan$1,200Check 1/1/191/4/19 $1,100.00 $50Paid50Paid$0$10012/24/19Check12003
Feb$1,300 2/1/192/7/19 $80Paid80Owed$0$1,300
Mar$1,400 3/1/193/11/19 $120Paid120 $120$1,520
Apr$1,100 4/1/194/12/19 $130 0 $130$1,230
May 5/1/19 $0 0 $0$0
<colgroup><col width="29" style="width: 22pt; mso-width-source: userset; mso-width-alt: 1060;"> <col width="45" style="width: 34pt; mso-width-source: userset; mso-width-alt: 1645;"> <col width="38" style="width: 29pt; mso-width-source: userset; mso-width-alt: 1389;"> <col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;"> <col width="49" style="width: 37pt; mso-width-source: userset; mso-width-alt: 1792;" span="2"> <col width="49" style="width: 37pt; mso-width-source: userset; mso-width-alt: 1792;"> <col width="59" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2157;"> <col width="49" style="width: 37pt; mso-width-source: userset; mso-width-alt: 1792;"> <col width="33" style="width: 25pt; mso-width-source: userset; mso-width-alt: 1206;"> <col width="38" style="width: 29pt; mso-width-source: userset; mso-width-alt: 1389;"> <col width="33" style="width: 25pt; mso-width-source: userset; mso-width-alt: 1206;"> <col width="38" style="width: 29pt; mso-width-source: userset; mso-width-alt: 1389;" span="2"> <col width="48" style="width: 36pt; mso-width-source: userset; mso-width-alt: 1755;"> <col width="56" style="width: 42pt; mso-width-source: userset; mso-width-alt: 2048;"> <col width="38" style="width: 29pt; mso-width-source: userset; mso-width-alt: 1389;"> <col width="40" style="width: 30pt; mso-width-source: userset; mso-width-alt: 1462;"> <col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;"> <tbody> </tbody>

So on the March payment of $120 it was paid on the 11th. hence the $120 payment goes to the owner. So "HelP!"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,658
Office Version
  1. 365
Platform
  1. Windows
So what exactly is your question?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,826
Messages
5,855,866
Members
431,771
Latest member
CoryMelth

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