sumifs with 2 dates and a rolling 12 month forecast

Stefan 7k

Hi Community, 

I'm now thinking about this already too long, so maybe you can help me out?

I​​​​t'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... 

Better ideas?

Best regards

Stefan

Stefan 7k

Solved.

Maybe someone is interested, so here is my solution. May not be the best, but it works.

I kind of "normalised" all dates in my sheet so that I can simply compare dates in my sumifs formula. 

So a date like 15.05.18 (= 15th of may 2018) is transformed to 01.05.18 using the "date" function.

Thanks for listening ;-)