sumifs with 2 dates and a rolling 12 month forecast
I'm now thinking about this already too long, so maybe you can help me out?
It's about controlling the total amount of guarantees active within the next 12 months (rolling forecast).
Every guarantee has a value, a starting date and a termination date.
Setting within 1 sheet:
Column1 = Euro guaranteed
Column2 = Start date (dd.mm.yy)
Column3 = End date (dd.mm.yy)
Column4 = 12 rows with dates for the next 12 months (dd.mm.yy)
Column5 = 12 rows with dates from C4 transformed to yyyy/mm
Column6 = problem! This should sum the values in C1 of all rows (aka guarantees) active in the next 12 months, summed up per month.
I will use this for a chart in a dashboard.
I've tried several ideas, but none worked due to having to parse through all values of a column (C1) and checking it's start and end date versus the dates in every row of C4.
Maybe I should try to transform the dates in C2 and C3 in a way, that "dd" is always 01...
Projektmanagement Professional in Frankfurt am Main, Germany
Help Article Resources
Check out the Formula Handbook template!