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.

195 votes
Sign in
(thinking…)
Sign in with: Facebook Google
Forgot password?
Create a password
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 →

33 comments

Sign in
(thinking…)
Sign in with: Facebook Google
Forgot password?
Create a password
Signed in as (Sign out)
Close
Close
Submitting...
An error occurred while saving the comment
  • 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),"<>")

  • First260 commented  ·   ·  Flag as inappropriate

    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  ·   ·  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.

  • JohnD commented  ·   ·  Flag as inappropriate

    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.

← Previous 1

Excel for Windows (Desktop Application): Formulas and Functions

Categories

Feedback and Knowledge Base

(thinking…)