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?

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...

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 ;-)