YTD using dynamic spilled array

Hi All, This is my 1st thread so please bear with me...

I am stuck with trying to calculate YTD using a spilled array formula

Cell B1 is a spilled formula from available months that have data in another sheet

Cells B2 & B3 are also spilled using using =sumifs & =countifs

What i need to do is have a formula that will add the YTD totals dynamically ie now it will add up jan to may. When June is added dynamically i want the formula to spill and add those figures in the relevant cell.

i would be really grateful for some advise on how to do this!

Good idea.... i have just created a small example file rather than the huge file i am working on

Thanks

Basic_Example.xlsx

Thanks Carim

Thats as far as i got but i need this to spill as the range grows. So the formulas only live in cells O6 & O7

Cells O3 O4 O5 contain the formula which automatically spill when data (new months) is added so when my target list (table1) grows and my sales grow (table2) the results will automatic expand.

Ill keep trying to find a solution and ill update this thread if i do as i think when the new dynamic arrays are rolled out to all 365 users this will be useful!

Thanks again for your help Carim

i can't get it to spill although it will calculate a spilled ref to a single cell just not an array

Had a solution on another forum which just needed tweaking a little to work.

Thought id share this as anyone using the new dynamic aware excel may come across the same issue.

Basic_Example.xlsx