Forum Discussion

A_B_Excel's avatar
A_B_Excel
Copper Contributor
Oct 21, 2022

SUMIF

Hi I am looking for help with a formula to calculate outstanding portfolio. 

 

Each investment has a maturity date a cash flow on the maturity. I want to calculate outstanding portfolio on each date in various category (<1 year, 2-3 years, etc).

 

I am trying this: =SUMIF(dateif(I$7,$E$6:$E$98,"y"),"<1",$C$6:$C$98)

 

Also I am not able to add screen shot here for some reason.

 

  

Thanks in advance,

excel
Excel on Mac

5 Replies

    • A_B_Excel's avatar
      A_B_Excel
      Copper Contributor
      Oct 21, 2022

      Hi HansVogelaar 

       

      Sorry, I was trying to upload the screen shot but can't seem to be able to do that.

       

      I7 - reference date for calculating outstanding amounts for various maturity

      E6:E98 - Maturity dates

      C6:C98 - Is principal amount that will be repaid on maturity.

       

      I want to calculate amount that will mature in in x years from I7.

       

      Thanks

      AB

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Oct 21, 2022

        A_B_Excel 

         

        Let's say "x" is in A10

         

        =SUM((DATEDIF(I$7,$E$6:$E$98,"y")<A10)*$C$6:$C$98)

         

        If you don't have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter.

        The formula can be filled down or to the right if you have other values of "x" below or to the right of A10.

Resources