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! :/


    Jan-19Feb-19Mar-19Apr-19May-19
    Target1012221816
    Actual1110211919
    YTD Target
    YTD Actual
  • Hello and Welcome to the Forum :)


    To make things easier for everyone ... why don't you attach your sample file ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

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

  • Unfortunately ( for me ...) cannot test the Spill feature of existing functions ...


    Based on what I have read, some standard functions do support the new Spill feature


    You could test in cell O6 the following : =SUM($O$4:AL4)


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Thanks for your comment ...


    Meanwhile, does it mean the SUM function does not ... yet ... support the Spill feature ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Administrative Note:


    Welcome to the forum. :)


    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.


    (Note: this requirement is not optional.)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules