Forum Discussion

Candi80's avatar
Candi80
Copper Contributor
Jan 04, 2024
Solved

if function help

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 tot...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jan 04, 2024

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

     

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

HansVogelaar's avatar
HansVogelaar
MVP
Jan 04, 2024

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

 

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

  • Candi80's avatar
    Candi80
    Copper Contributor
    Jan 04, 2024

    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.

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Jan 04, 2024

      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?)

       

      • Candi80's avatar
        Candi80
        Copper Contributor
        Jan 04, 2024

        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?

Resources