L_ROWSUM and L_COLSUM

=L_ROWSUM(matrix)
=L_COLSUM(matrix)

Download the Template

In the template file, navigate to the Matrices worksheet to see the L_ROWSUM and L_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. L_ROWSUM and L_COLSUM are essentially building block utilities that may be used within other more advanced functions.

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

L_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 L_ONES:
=MMULT(matrix,L_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)))

L_COLSUM is just as simple:

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

Lambda Formula

This code for using L_ROWSUM and L_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.

Name: L_ROWSUM
Comment: Returns a column vector containing row sums
Refers To:
=LAMBDA(matrix,
LET(doc,"https://www.vertex42.com/lambda/rowsum-and-colsum.html",
    BYROW(matrix,LAMBDA(row,SUM(row)))
))

Name: L_COLSUM
Comment: Returns a row vector containing column sums
Refers To:
=LAMBDA(matrix,
LET(doc,"https://www.vertex42.com/lambda/rowsum-and-colsum.html",
    BYCOL(matrix,LAMBDA(col,SUM(col)))
))

/**
* Returns a column vector containing row sums
*/
L_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
*/
L_COLSUM = LAMBDA(matrix,
LET(doc,"https://www.vertex42.com/lambda/rowsum-and-colsum.html",
    BYCOL(matrix,LAMBDA(col,SUM(col)))
));

Function: L_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: L_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)))
)

L_ROWSUM and L_COLSUM Examples

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

Result: {11;22}

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

Result: {3,30}