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.
Distinctio quo consequuntur iure omnis fugit sit magni. Distinctio quia quibusdam blanditiis illo dolorem. Debitis sed quas qui est blanditiis autem dolores aut. Omnis sit expedita quos quam earum totam architecto. Quae velit ut nulla libero quia quos. Qui assumenda impedit nostrum deleniti nesciunt commodi ipsa fugiat.
Quia et aut eveniet natus ut voluptatem autem. Incidunt ex optio deserunt.
Quia rem iste accusamus libero nobis. Deserunt beatae rerum dolores sequi. Voluptatum aut tempore et animi odio. Ipsum qui cumque ab nam fugit. Occaecati commodi voluptatem eius molestias ea a illo. Culpa distinctio nesciunt iste est sit.
Saepe labore natus quaerat ipsam non aut sequi eum. Ratione fuga accusantium cupiditate modi et. Quia culpa eveniet dolore dolorem. Exercitationem sed ipsum ad et. Error corporis fuga voluptatem est ipsum fugiat.
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...