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>
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
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
46,340
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
46,340
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
46,340
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
46,340
Office Version
  1. 365
Platform
  1. Windows
So what exactly is your question?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,028
Messages
5,526,332
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top