Dynamically expanding SUMIFS based on SEQUENCE range

user_18619717 0
Viewed 66

My first question on here, apologies in advance for any missing information, happy to provide anything else needed.

I have a forecast to predict when a target number of satisfaction surveys will be reached in the current quarter.

I currently have a SEQUENCE formula set up that will create a range of date cells based on the current quarter up to and including today

=SEQUENCE(COUNTOFDAYSINYEAR,1,STARTDATE,1)

COUNTOFDAYSINYEAR is just a simple DAYS360 formula to get the number of days up to today in the current quarter, and all that is needed is to update the STARTDATE with the start date of the current quarter.

Next to this dynamic range I have a SUMIFS column that work fine, but Excel doesn't automatically add a new SUMIF row when the SEQUENCE date range moves down daily, and I can't make the data into a table to solve the problem as it gives me a Spill error. Any ideas how I can get the SUMIFS to automatically expand down the SEQUENCE list as it grows and shrinks through the quarters of the year?

enter image description here

excel-formula excel
1 Answers

It's hard to replicate your formulae without data, so I pieced together the following anticipating ms365 (hence the use of SEQUENCE()):

enter image description here

Formula in D1:

=LET(X,SEQUENCE(3),CHOOSE({1,2},X,SUMIF(A1:A6,X,B1:B6)))

Or, if available to you:

=LET(X,SEQUENCE(3),HSTACK(X,SUMIF(A1:A6,X,B1:B6)))

I've shown you this trick to allow for a 2nd column in the spilled array that would automatically adjust if the X variable would expand.

user_97581940
Related