SUMCOLS and SUMROWS functions for Dynamic Arrays
Create new functions that SUM each column in a dynamic array (SUMCOLS(Ref#)) to produce a row of totals, and SUM each row to produce a column of totals (SUMROWS(Ref#))
Also COUNTCOLS, COUNTROWS, AVERAGECOLS, AVERAGEROWS, but these are less important than SUMCOLS & SUMROWS.
Jason Webber commented
This is critical to truly unlock the power of dynamic arrays for financial modelling.
I agree with Charles' sentiment that just summing a column and then copying across defeats the purpose of dynamic arrays entirely.
I also like the suggestion to add BYROW / BYCOL functions that would return arrays of row or column ranges. Then for an array of column sums one could use:
I'd suggest generalising the concept by adding a dimension argument to aggregation functions (like in numpy / matlab). The dimension argument would refer to rows (1), columns (2) or areas (3) for multi-area or 3D ranges. Examples might include
=XSUM(Table,1) returns an array of row sums
=XMIN(Table,2) returns an array of column minima
=XAGGREGATE(1,,3,Tables) returns a 2d array aggegating tables over different areas or sheets
(hopefully i understand the issue described by everyone's post)
Personally, my issue is not about the expansion as i can use:
=SUM(B5#) * NOT(COLUMN(B5#)=FALSE)
which does expand because of the COLUMN function
(and the "*NOT( )" always returns *TRUE = *1)
-- same idea with rows and ROW ( )
But when i use the intersection operator (space) to reference a single column:
B5# B:B = intersection of the B5# array and column B:B
=SUM(B5# B:B) * NOT(COLUMN(B5#)=FALSE)
the B:B doesn't auto update to C:C, D:D... I do understand why: all cells of the dyn array share the same formula.
So i guess my request would be a function that returns the reference to the column :
TheColumn(B5#) returns B:B in the first cell, C:C in the second cell, and so on.
In the end , i could just write:
=SUM(B5# TheColumn(B5#) ) * NOT(COLUMN(B5#)=FALSE)
= SUM(B5# TheColumn(B5#) ) where TheColumn forces the horizontal expansion
This way, it works with SUM, COUNT, ...
Kenneth Barber commented
I agree with Harlan Grove. I, too, prefer minimalism in this case. I already don't like how we have -IF, -IFS, and D- variants of SUM, COUNT, AVERAGE, etc., whereas we only need FILTER and then we just go SUM(FILTER()). To have a row variant and a column variant of each aggregation function would be insane. The function list would get very long and intimidating.
This is a good example of why higher-order functions should be introduced to Excel. Then we would only need 2 more functions: ForEachRow and ForEachColumn.
We could have something like ForEachRow(SUM,<array>) instead of SUMROWS(<array>). That is, we pass the SUM function itself to a function that applies the passed function to each row and returns a column of results.
And let's not forget that PivotTables have been doing the dynamic-sized total range since forever. They just require the input to be in a table, which, in my opinion, is how everyone should be setting up their spreadsheets anyways.
Charles Williams commented
Its probably better to have total type as a parameter like aggregate.
Floating or fixed column totals: Totals each column of the Table_Range/Expression.
Floating produces a copy of the Table_Range/Expression with a horizontal array of the totals of the columns appended at the bottom.
Fixed produces a fixed position horizontal array of the totals of the columns without a copy of the table/range.
Dynamically adjusts to the number of rows and columns in Table_Range
TOTALS (Table_Range [, Exclude_Cols] [,TotalTypes], [Fixed] )
Viswanathan M B commented
Can we also have MinRow, MinCol, MaxRow and MaxCol? As of now, the only way to do that is using an IF function, which, although intuitive, is not an ideal option.
Jeff Robson commented
Totally agree! MMULT is a terrible solution to such a simple problem! Thanks for suggesting this Charles!
When DA’s were first announced, I felt this feature was missing, but I couldn’t think how to solve it.
Charles’ suggestion would be perfect.
Would it make sense to use names like SUMC for columns and SUMR for rows (and COUNTC, COUNTR etc), rather than the longer SUMCOLS and SUMROWS? Less typing.
great Idea... 👌👌
I have shared this with my Instagram followers (www.instagram.com/softwaretrain) to vote.
Hope be voted and soon have it in Excel internally.
Really good Idea