SUMIF

Copper Contributor

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,

5 Replies

@A_B_Excel 

What do I7, E6:E98 and C6:C98 contain?

Hi @Hans Vogelaar 

 

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

@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.

Hi, I have a similar but slightly expanded problem. I have a table with income coming in from an investment portfolio. In the table I have Type (which are 'Dividend', 'Interest', or 'Principal'. I also have the date of the payment and have calculated the number of days to payment. I want to summarize the payments in a table that distinguishes between 'Interest', 'Dividend' and 'Principal' and I want to days to payment by <30, 30 to 90, 90 to 180, 180 to 360, and greater than 360. The following formula returns 0. =SUMIFS(CF[Total
CF],CF[Days to
Payment],"<A56",CF[Type],"=Dividend",CF[Type],"=Interest")

@beirnegroup 

I assume that cell A56 contains a number. If so:

 

=SUMIFS(CF[Total
CF],CF[Days to
Payment],"<"&A56,CF[Type],"=Dividend",CF[Type],"=Interest")