Feedback by UserVoice

 

How can we improve Excel for Windows (Desktop Application)?

← Excel for Windows (Desktop Application)

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.

300 votes

We're glad you're here

Please sign in to leave feedback
Signed in as (Sign out)
Close
Close

We’ll send you updates on this idea

Charles Williams shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

44 comments

We're glad you're here

Please sign in to leave feedback
Signed in as (Sign out)
Close
Close
Submitting...
An error occurred while saving the comment
  • Eduardo Cervantes commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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)

  • Chaim Gewirtz commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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)))

  • Jaime Segura commented  ·   ·  Flag as inappropriate

    I can offer an VBA alternative (need to be copied on a module):

    Option Explicit
    Option Base 1

    Function 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 = vector

    End Function

  • A.J. Wilkes commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    Still Intrigued why these have not been implemented as yet.

    Red Tape-ism ? Who Said ?

  • Jason Webber commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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),"<>")

  • sebastienm commented  ·   ·  Flag as inappropriate

    (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 expansion

    This way, it works with SUM, COUNT, ...

  • Kenneth Barber commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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] )

  • Jeff Robson commented  ·   ·  Flag as inappropriate

    Totally agree! MMULT is a terrible solution to such a simple problem! Thanks for suggesting this Charles!

  • Mark commented  ·   ·  Flag as inappropriate

    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.

← Previous 1 3

Excel for Windows (Desktop Application): Formulas and Functions

Categories

Feedback and Knowledge Base

(thinking…)