if function help

[Candi80]

I need help to get this function to work correctly! Right now it only subtracts the unit cost (G) if there is a date in (M)I need it to subtract both Unit cost(G) and Repairs invoices(H) from the total. for Line G26.

Does that make sense? 

Thanks in advanced.

[#2 · Accepted solution]

Instead of subtracting, we can sum only the rows for which column M is blank.

=SUMPRODUCT(G2:H24*(M2:M24=""))

[#3]

I have 1 more question. Daily interest i have as =(G2*0.0925)/360, How can I get a formula to calculate so the total changes with today's date.  So every time i open it the total will be current from date purchased?

Thanks again if you can help.

[#4]

If the interest is fixed at 9.25%/360 per day, you can use

=G2*(TODAY()-A2)*9.25%/360

If you want to calculate cumulative interest:

=FV(9.25%/360, TODAY()-A2, 0, -G2)-G2

This will be slightly higher since the interest is added to the unit cost.

(Shouldn't it be 365 or 366 instead of 360?)

[#5]

The bank charges by 360. That is what the loan guy said to use for calculations. Could I make this a if function? To add if there is a date in (M) use that date instead of today's date?

[#6]

=G2*(IF(M2="", TODAY(), M2)-A2)*9.25%/360

[#7]

You are so Smart... Thank you Thank you Thank you. Made my life easier.