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.
Pariatur aut est magni. Fuga quam aut expedita alias officia est. Eum id veniam sunt quam dolores impedit expedita. Beatae ut sit qui ut. Harum facilis dolores voluptatem et ipsam. Sit aut sapiente sed nobis cupiditate doloribus.
Nulla eaque et officia repudiandae esse qui. Fugiat dolorem consequuntur optio et minus laudantium dolores. Et ut culpa ex vero nisi.
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...