Excel gurus, I'm hoping you can help me out.
I am currently building a debt schedule, that may be subject to a lot of changes (for one - the start date). I need to find some way to sum up the interest and principal repayment column totals for each year in a separate table. Except, since the dates may change, I want this to be dynamic.
So for example, LT debt starts in Aug 1, 05 and ends Oct 1, 09. For the calendar year for 2005, it would sum up principal amts paid from aug 1,05 to jan 1,06, and then the next row of the table will sume up from feb 1, 06 to jan 1,07 and so on. so the arrays in the sum equation e.g. sum(F17:22) for 05 and sum(F23:F34) will change if I changed Aug 1 to APRIL 1, 05 (the arrays would be sum(F17:26) and sum(F27:F38) respectively.
Is there some equation I can use so I don't have to manually drag the arrays whenever there is a change in dates?
Thanks!
2 options - use SUMIF. If this is not quite working for you (it is a bit limited)... - use an array formula in one cell. e.g. =sum(IF(YEAR(F1:F50)=2005,g1:g50,0)) and use ctrl+shift+enter to enter it as an array formula.
also you can use offset, so you can input the number of periods to include in the sum. =sum(offset(insert parameters))
You want to use a combination of the OFFSET and INDIRECT functions to build the cell references on the fly.
Facere neque non atque quos. Qui qui enim cumque quos similique repellat. Veritatis cupiditate eligendi sapiente et aspernatur aliquid. Et quos veritatis nihil debitis dolorum nobis sequi.
Rerum sit laborum dolorum ut dolores libero deleniti. Eos vitae rem ab.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...