i can't get it to spill although it will calculate a spilled ref to a single cell just not an array
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
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.
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!
Jan-19 Feb-19 Mar-19 Apr-19 May-19 Target 10 12 22 18 16 Actual 11 10 21 19 19 YTD Target YTD Actual