Summing totals in a dynamic list

[bearcub]

I have a dynamic formula that I created that works great except except for the totaling the columns.

I am pulling data from another sheet and creating a YTD month report that can been filtered by month. I would like to add a Totals row . I can get the total to show up at the end of the list but Choose function I added to the original formula without the totals isn't summing the columns properly.

Here is the formula that I am using. I thinking that maybe I need to replace the Choose function with the VSTACK as an array argument.

I don't know which Addition formulas (e.g. Sum, SumIf (s), Sumproduct) would best to use as an arrays argument inside VSTACK - if VSTACK is the correct function to use.

Note:

March!L2 is the month that I would be filtering inside the filter function, Bookings U4:U50 is the source range of the report.

I added the Let function to my formula so I could add the VSTACK function. Is there a more efficient way to create a dynamic "Total" row? The Sum(index) formulas are not providing the correct column totals. I have no idea where the column totals are being derivedView attachment 110641

=LET(a,

CHOOSECOLS(SORT(FILTER(

FILTER('FY25 Bookings'!K4:U50,('FY25 Bookings'!V4:V50>=March!L2)*('FY25 Bookings'!U4:U50<>0)),

{1,0,1,1,1,1,1,1,1,1,1}),10,-1),

1,10,2,4,6,7,3),

VSTACK(a,

CHOOSE({1,2,3,4,5,6,7},

Total,

SUM(INDEX(a,2)),

SUM(INDEX(a,3)),

SUM(INDEX(a,4)),

SUM(INDEX(a,5)),

SUM(INDEX(a,6)),

SUM(INDEX(a,7)),

)))

Everything after the VSTACK- other than the "Total" - is not behaving the way that I need to work.

Like I mentioned, the array formula behaves correctly until I get the "SUM(Index ..."" formula housed inside the Choose function (which is nested inside the VSTACKfunction).

I added the Let function when I decided to add VSTACK. VSTACK was added to create a dynamic Total Row.

 

[Cubist]

VSTACK your "a" with this.

BYCOL(a,LAMBDA(br,SUM(br))

 

[Fluff]

How about

=LET(a,SORT(CHOOSECOLS(FILTER('FY25 Bookings'!K4:U50,('FY25 Bookings'!V4:V50>=L2)*('FY25 Bookings'!U4:U50<>0)),1,11,3,5,7,8,4),2,-1),VSTACK(a,HSTACK("Total",BYCOL(DROP(a,,1),LAMBDA(bc,SUM(bc))))))

Your sum sections are adding rows, not columns.

 

[bearcub]

I didn't even think about adding versus columns. The thought never occurred to me.

Thank you, this is wonderful. What does bc stand for in the Lamba function (by column)? Where is that defined in the formula.

If I wanted to include an IsError function in the formula would is just add it normally. My fear is the the IsError function will impact the array results (i.e. only showing one line).

 

[Fluff]

What does bc stand for in the Lamba function (by column)?

It's just a variable & you can call it what you want.

If I wanted to include an IsError function in the formula would is just add it normally.

Just depends on what you want to trap.