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.

19 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 →

8 comments

Attach a File
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
  • Charles Williams commented  ·   ·  Flag as inappropriate

    @Kevin,

    Would be nice to address your DA Table problem, but that would not solve the problem for general DA totals.

  • Kevin Osborn commented  ·   ·  Flag as inappropriate

    I understand where you are going with this but I'm not going to vote it up. IMO a better solution is to allow 1 dynamic array function inside a table. Not only will this address your issue with sum/count/avg for columns because tables can have a "total" row but it will also fix the deficiency in dynamic arrays whereby structured/table reference cannot be used to reference a column in a dynamic array so the developer is forced to revert back to column numbers. Similarly instead of having sum, count, average rows I'd rather see a "Row Total" added to tables (similar to column totals).

  • Peter Bartholomew commented  ·   ·  Flag as inappropriate

    Hi Charles

    I fully support this initiative. I had posted a related idea but I am not too fussed about the implementation provided the functionality is there.

    https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/36151087-aggregate-2d-arrays-by-row-or-by-column-especiall

    My aim is not simply to use DA here and there but to build dynamic solutions from the ground up using nothing but dynamic arrays. At the moment my solutions are full of matrix multiplication (MMULT) to perform aggregations by row and by column and to accumulate arrays in either direction.

    There is no chance of rolling such methods out across the Excel community, so a suite of new functions is essential to the success of DA.

    It is such a relief to move away from manual knife-and-fork selection of cells and rows for calculation (more accurately selection by mouse and keyboard shortcuts) and let the calculation logic speak for itself based upon references to entire arrays and tables.

  • Roger Govier commented  ·   ·  Flag as inappropriate

    Excellent idea Charles and I have tested your own functions that you have already written which work perfectly.
    However, I think it would be even more useful if there was a way to pass an optional array to the function to excluded certain columns otherwise you end up summing dates and unit prices etc., which is neither use to man nor beast.
    e.g. =SUMCOLS(E7#,{1:6,9}) which would exclude summing the first 6 columns and the 9th for example.

  • Jeff commented  ·   ·  Flag as inappropriate

    Definitely needed! MMULT is not intuitive or easy for the average user

Excel for Windows (Desktop Application): Formulas and Functions

Categories

Feedback and Knowledge Base

(thinking…)