ROWSUM and COLSUM

=ROWSUM(matrix)
=COLSUM(matrix)

Download the Template

In the template file, navigate to the Matrices worksheet to see the ROWSUM and COLSUM function in action.

Description

Although not difficult to do with regular Excel functions, finding the sum of rows and columns is a common need, so it can be useful to have dedicated functions for the purpose. ROWSUM and COLSUM are essentially building block utilities that may be used within other more advanced functions.

ROWSUM returns a column vector containing the sums of each separate row.

COLSUM returns a row vector containing the sums of each separate column.

There are many ways to calculate the sum of each row. For example, multiplying an nxm matrix by an mx1 vector of ones will return the row sums:

Pre-Dynamic Arrays:
=MMULT(matrix,INDEX(1+0*MUNIT(COLUMNS(matrix)),0,1))

Using SEQUENCE:
=MMULT(matrix,SEQUENCE(COLUMNS(matrix),1,1,0))

Using ONES:
=MMULT(matrix,ONES(COLUMNS(matrix),1))

A slightly more robust method is to use the BYROW function with a very simple LAMBDA that uses SUM on each row. If the array contains any text, it is ignored (unlike the matrix multiplication method).

Using BYROW:
=BYROW(matrix,LAMBDA(row,SUM(row)))

COLSUM is just as simple:

=BYCOL(matrix,LAMBDA(col,SUM(col)))

Lambda Formula

This code for using ROWSUM and COLSUM in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

/**
* Returns a column vector containing row sums
*/
ROWSUM = LAMBDA(matrix,
LET(doc,"https://www.vertex42.com/lambda/rowsum-and-colsum.html",
    BYROW(matrix,LAMBDA(row,SUM(row)))
));
/**
* Returns a row vector containing column sums
*/
COLSUM = LAMBDA(matrix,
LET(doc,"https://www.vertex42.com/lambda/rowsum-and-colsum.html",
    BYCOL(matrix,LAMBDA(col,SUM(col)))
));

Function: ROWSUM
Description: Returns a column vector containing row sums
Arguments: matrix
LET(doc,"https://www.vertex42.com/lambda/rowsum-and-colsum.html",
    BYROW(matrix,LAMBDA(row,SUM(row)))
)

Function: COLSUM
Description: Returns a row vector containing column sums
Arguments: matrix
LET(doc,"https://www.vertex42.com/lambda/rowsum-and-colsum.html",
    BYCOL(matrix,LAMBDA(col,SUM(col)))
)

ROWSUM and COLSUM Examples

Test: Copy and Paste this LET function into a cell
=LET(
    matrix, {1,10;2,20},
    ROWSUM(matrix)
)

Result: {11;22}

=LET(
    matrix, {1,10;2,20},
    COLSUM(matrix)
)

Result: {3,30}