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.
Charles Williams
shared this idea
45 comments
-
Kevin Jones
commented
One new function that might just give you all what you want without changing any existing functions or adding new ones:
It's similar to Chaim Gewirtz's TABLECOL idea proposed in this thread.
-
Eduardo Cervantes
commented
You may want to try this for averages per column (assuming A1# is the dynamic range with the source data):
=SUBTOTAL(1,OFFSET(A1#,0,SEQUENCE(1,COLUMNS(A1#))-1,ROWS(A1#),1))
Just change the first argument of the SUBTOTAL function to any of the aggregation functions available. With some tweaks, this can be made to work for row totals
-
First260
commented
Preferable to avoid OFFSET with hard-coded references and also allow for blanks or text in range like SUM,
=MMULT(1^TRANSPOSE(ROW(matrix)),N(+matrix))
Now with LAMBDA one can define like a built-in function without being distracted by implementation details, eg
=SUMROWS(B2:D3)
-
Anonymous Coward
commented
Apparently this has always been possible with CSE's
=MMULT(COLUMN(OFFSET($A$1,,,,ROWS(Matrix)))^0,Matrix)
-
Chaim Gewirtz
commented
I would simply create a new function TABLECOL. Like this:
TABLECOL(Table, StartRow (optional), EndRow (optional))
' Returns the column in the table that matches up with the current cell.
'
' args:
' Table: The table (or range)
' StartRow: The first row to return.
' EndRow: The last row to return. A negative number signifies counting backwards from the bottom.The TABLECOL function would be flexible and allow simple, powerful operations such as:
=AVERAGE(TABLECOL(Table))
=SUM(TABLECOL(Table, 10, -5)) -
First260
commented
This is no longer an issue, just need to insert previous formulas inside LAMBDA in the name definition:
SUMCOLS =LAMBDA(x,MMULT(SEQUENCE(1,ROWS(x),1,0),x))
SUMROWS =LAMBDA(x,MMULT(x,SEQUENCE(COLUMNS(x),1,1,0)))
-
Anonymous Coward
commented
Make UDFs
SUMCOLS(x) = MMULT(SEQUENCE(1,ROWS(x),1,0),x)
SUMROWS(x) = MMULT(x,SEQUENCE(COLUMNS(x),1,1,0))
-
Jaime Segura
commented
I can offer an VBA alternative (need to be copied on a module):
Option Explicit
Option Base 1Function SUMCOLS(data As Variant) As Variant
Dim mat As Variant
Dim rws As Long, cls As Long
Dim i As Long, j As Long
Dim vector As Variant
' transfer data from range/matrix to matrix
mat = data
' in case original data was a single cell
If Not IsArray(mat) Then
ReDim mat(1, 1)
mat(1, 1) = data
End If
' get size of matrix
rws = UBound(mat, 1)
cls = UBound(mat, 2)
' create and fill vector
ReDim vector(1, cls)
For i = 1 To rws
For j = 1 To cls
vector(1, j) = vector(1, j) + Val(mat(i, j))
Next j
Next i
' return vector as answer
SUMCOLS = vectorEnd Function
-
Gaetan Mourmant
commented
I believe JOINTEXTROWS and JOINTEXTCOLS would also be a great addition.
-
A.J. Wilkes commented
Yes! For business users (Accountants) we often like to have a sum of a column above the header of a table that's not really a table and the last row of the data can change from month to month. Using an absolute reference doesn't work because if rows below are deleted then the absolute range adjusts.
One can use an indirect range reference =SUM(INDIRECT("P3:P1048576",TRUE)) but this cannot be copied to another column without changing the "P" in the range string. One can also use a SUBSTITUTE to get the column letter to create the range string, but that is unwieldy.
Ideally, there would be a SUMCOL function with a starting reference cell that always assumes down to the last row of that column.
-
Bronwyn Boltwood
commented
Yes add these please for the love of [insert deity here]. I have been driving myself mad searching for how to do this without repeating the entire filter definition for each sum.
Since my office uses the super-stodgy update channel, I had figured there must already be a way. It didn't occur to me that this feature didn't exist yet!
-
Anonymous
commented
Still Intrigued why these have not been implemented as yet.
Red Tape-ism ? Who Said ?
-
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.
-
First260
commented
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:
=SUMIF(BYCOL(Table1),"<>") -
First260
commented
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 -
sebastienm
commented
(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)
even better:
= SUM(B5# TheColumn(B5#) ) where TheColumn forces the horizontal expansionThis 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!