Combine sum + offset + countif

[parreich]

Hi everyone,

I need a formula to sum the cash flow for debt service row only the years that I have debt repayments.

Can you give me any help?

Thanks.

[Richard Buttrey]

Hi,

You may need to clarify with an example. I'm assuming any number on row 16 is a debt repayment and by sum you mean a cumulative sum, but clarify if necessary.

In say G18 copied across

Please Login or Register  to view this content.

[parreich]

What I really want to do is to calculate the NPV of the Cash flows for debt service but only including in the range the years that I have debt repayments.

I need to take out of this range +NPV(5%;$H$15:$AE$15) the years that I don't have debt repayments.

[parreich]

I Think this should function: NPV(0,05;H15:OFFSET(H15;0;COUNTIF(H19:AE19;"<0")))

[Richard Buttrey]

Hi,

Please remember to be as accurate in your request as possible. Your first post merely mentioned summing the cash flows whereas it now seems you are wanting a NPV calculation.