Excel LAMBDA Spotlight: SumColumnsλ

In this article I'll look at a LAMBDA function called SumColumnsλ, written by Craig Hatmaker . Craig is very active in the Excel community and is an active proponent of component-based software engineering (CBSE) for LAMBDA development.

The function I'm writing about today is somewhat simple, but I chose this one because it highlights a few of the techniques that Craig is using to implement what I would call quality of life improvements for developers and users.

I encourage you to follow Craig on LinkedIn and his gist home page.

You can find the gist for this and several other functions here.

OVERVIEW

SumColumnsλ takes its single argument - an array - and applies the SUM function to each of the columns.

FUNCTION DEFINITION

/* ?FUNCTION NAME: ?SumColumnsλ
? ? DESCRIPTION:*//**Totals an array's columns*/
/* ?REVISIONS: ? ? ?Date ? ? ? ? ? ?Developer ? ? ? Description ?
? ? ? ? ? ? ? ? ? ? Mar 06 2023 ? ? Craig Hatmaker ?Original Development
? ? ? ? ? ? ? ? ? ? Apr 10 2023 ? ? Craig Hatmaker ?Added Help
*/


SumColumnsλ = LAMBDA(
// ?Parameter Declarations
? ? [Array],
? ? 
// ?Procedure
? ? LET(Help, ? ? ? TRIM(TEXTSPLIT( 
? ? ? ? ? ? ? ? ? ? ? ? "DESCRIPTION: ? →Creates totals for each of an array's columns.?" & 
? ? ? ? ? ? ? ? ? ? ? ? "VERSION: ? ? ? →Apr 10 2023?" &
? ? ? ? ? ? ? ? ? ? ? ? "PARAMETERS:→?" & 
? ? ? ? ? ? ? ? ? ? ? ? "Array ? ? ? ? ?→(Required) A two dimensional array/range containing values to be summed.?" & 
? ? ? ? ? ? ? ? ? ? ? ? "→?" &
? ? ? ? ? ? ? ? ? ? ? ? "EXAMPLES:→?" & 
? ? ? ? ? ? ? ? ? ? ? ? "Result ? ? ? ? →Formula?" &
? ? ? ? ? ? ? ? ? ? ? ? "15,25,35,45 ? ?→=SumColumnsλ({1,2,3,4;4,3,2,1;10,20,30,40})" ,
? ? ? ? ? ? ? ? ? ? ? ? "→", "?" )
? ? ? ? ? ? ? ? ? ? ),
? ? // ?Check inputs
? ? ? ? Array, ? ? ? ? ?IF(OR(ISOMITTED(Array), Array=""), #Value!, Array), ? ?


? ? // ?Procedure
? ? ? ? Result, ? ? ? ? BYCOL(Array, LAMBDA(Column, SUM(Column))),


? ? // ?Handle Error
? ? ? ? Error, ? ? ? ? ?OR( ISERROR( Result)) + 1,
? ? ? ? 
? ? // ?Return Result
? ? ? ? Choose(Error, Result, Help)
? ? ) ? ? ? ?
);        

SumColumnsλ takes one optional parameter:

1. Array - an array or range

BREAKDOWN

SumColumnsλ = LAMBDA(
// ?Parameter Declarations
? ? [Array],
? ? 
// ?Procedure
? ? LET(Help, ? ? ? TRIM(TEXTSPLIT( 
? ? ? ? ? ? ? ? ? ? ? ? "DESCRIPTION: ? →Creates totals for each of an array's columns.?" & 
? ? ? ? ? ? ? ? ? ? ? ? "VERSION: ? ? ? →Apr 10 2023?" &
? ? ? ? ? ? ? ? ? ? ? ? "PARAMETERS:→?" & 
? ? ? ? ? ? ? ? ? ? ? ? "Array ? ? ? ? ?→(Required) A two dimensional array/range containing values to be summed.?" & 
? ? ? ? ? ? ? ? ? ? ? ? "→?" &
? ? ? ? ? ? ? ? ? ? ? ? "EXAMPLES:→?" & 
? ? ? ? ? ? ? ? ? ? ? ? "Result ? ? ? ? →Formula?" &
? ? ? ? ? ? ? ? ? ? ? ? "15,25,35,45 ? ?→=SumColumnsλ({1,2,3,4;4,3,2,1;10,20,30,40})" ,
? ? ? ? ? ? ? ? ? ? ? ? "→", "?" )
? ? ? ? ? ? ? ? ? ? ),        

You'll notice here that the Array parameter is defined as optional (it's enclosed in square brackets). The reason for this will become clear later.

LET Help be the array shown. Here there are some standard text fields which Craig is diligently adding to each of the functions he develops.

TEXTSPLIT is used on a long text string which is at times punctuated with a right-facing arrow (→) or a new line symbol (?). These two symbols are used as the column-delimiter and row-delimiter arguments. The TRIM function is then added to remove any whitespace. This is a neat way to have both:

1) aligned and readable notes in the Advanced Formula Environment, and

2) text output in the grid without trailing spaces

You can see that if I use the function without an argument, the help is displayed:

I have to say, I really like this.

Just before we move on, notice in the snippet above that the sections of the function are annotated and separated into logical blocks:

// ?Parameter Declarations
... ? 
// ?Procedure
...
etc        

This makes for a very pleasant experience when trying to understand the function for the first time.

Up next:

? ? // ?Check input
? ? ? ? Array, ? ? ? ? ?IF(OR(ISOMITTED(Array), Array=""), #Value!, Array), ? ?


? ? // ?Procedure
? ? ? ? Result, ? ? ? ? BYCOL(Array, LAMBDA(Column, SUM(Column))),


? ? // ?Handle Error
? ? ? ? Error, ? ? ? ? ?OR( ISERROR( Result)) + 1,
? ? ? ? 
? ? // ?Return Result
? ? ? ? Choose(Error, Result, Help)
? ? ) ? ? ? ?
);        

If you review any of Craig's recent functions, you'll note that he is checking the input of each parameter. This is good practice to avoid unexpected experiences for the user.

In this case, he checks if Array is either omitted or empty and returns the #VALUE! error if so. Otherwise the argument Array is passed into a variable of the same name. One issue here is that if any cell in Array is empty, this expression will return the #VALUE! error. So in this case it might be more safe to use:

? ? // ?Check input
? ? ? ? Array, ? ? ? ? ?IF(OR(ISOMITTED(Array), AND(Array="",ROWS(Array)=1,COLUMNS(Array)=1)), #Value!, Array),        

This will allow single cells in multi-cell arrays to be blank without causing a problem.

Moving on:

? ? // ?Procedure
? ? ? ? Result, ? ? ? ? BYCOL(Array, LAMBDA(Column, SUM(Column))),


? ? // ?Handle Error
? ? ? ? Error, ? ? ? ? ?OR( ISERROR( Result)) + 1,
? ? ? ? 
? ? // ?Return Result
? ? ? ? Choose(Error, Result, Help)
? ? ) ? ? ? ?
);        

The procedure itself is simple: by column, sum each column one by one. The result of this operation is saved as Result. Note that if the argument Array was omitted, then the variable Array contains the #VALUE! error. As such, Result will return an error.

Next, check if any cell in Result contains an error (remember, if Array was not omitted, Result is a single-row array with column count equal to Array), return the value 2. ISERROR applied to an array will return an array of the same size and OR will convert it to a single value. If this value is TRUE, it is equivalent to 1 when acting as an arithmetic operand. Hence, the Error value is 2. If Result doesn't contain an error, then Error returns 1.

There is one slight drawback with this approach - if any of the values in the columns being summed are error values, then the SUM for that column is also an error, and the Result contains a single error, and so the OR also returns an error, meaning the function returns the help text. Additionally, an error in any cell in the Array will cause the parameter check with Array="" to have an error in that cell, meaning the OR(ISOMITTED(... section will always return an error.

To mitigate both of these points, we can add IFERROR in two places:

1) in the first argument of AND, and

2) inside the SUM function in BYCOL

? ? ? ? Array, ? ? ? ? 
? ? ? ? ? ? IF(
? ? ? ? ? ? ? ? OR(
? ? ? ? ? ? ? ? ? ? ISOMITTED(Array), 
? ? ? ? ? ? ? ? ? ? AND(IFERROR(Array,0)="",ROWS(array)=1,COLUMNS(array)=1)
? ? ? ? ? ? ? ? ), 
? ? ? ? ? ? ? ? #VALUE!, 
? ? ? ? ? ? ? ? Array
? ? ? ? ? ? ), ? ?


? ? // ?Procedure
? ? ? ? Result, ? ? ? ? BYCOL(Array, LAMBDA(Column, SUM(IFERROR(Column,0)))),?        

These changes together allow blank cells and #VALUE! errors to not disrupt the calculation of the columns:

I should add that this interpretation of ignoring errors is mine only and it may be that not returning a sum at all in the presence of an error in the column being summed is more appropriate.

? ? ? ? 
? ? // ?Return Result
? ? ? ? Choose(Error, Result, Help)
? ? ) ? ? ? ?
);        

The final statement is to return either the Result array, in the case where Error=1 or the Help array, for when Error=2.

So now I hope it's clear that by omitting the function's argument, it pushes an error through the calculation and results in the return of the Help array.

All in all I think there are several great takeaways here:

1) Readability of a function definition is important

2) Detailed help text with examples is very useful

3) By using omitted argument syntax, we can provide a way for the user to display help instead of the function result

That's it for now. I hope you enjoyed this article. Please take a moment to follow Craig on LinkedIn and on gist.github.com using the links at the top of this article.

This is the seventh in the LAMBDA Spotlight series. If you haven't read the others, check them out:

A.XMATCH.ROWS

Text.DropSliceBetween

CROSSJOIN

UNPIVOT

SHOWCLOCK

xlMatrix LAMBDA library

You can see the other articles I've written?here.

If you have a suggestion for a function which you'd like put under the spotlight, please message me.

Thanks for reading!