A lambda to simplify the generation of subtotals by column from dynamic arrays.

Development Rationale

The purpose in developing this lambda is to simplify the derivation of vertical subtotals from an array (most likely, a dynamic array).  Using the SUBTITAL function and referring to an array of values will generate a subtotal for the entire range.  It does not automatically process the array column-by-column as might be desired.  A similar problem arises for horizontal subtotals and that is addressed in the separate λ Library 2.0: λSubtotalByRow article.

Suppose you had an array in the range B2:M15 that forecast values over a twelve month period.  You may wish to add a total row to the bottom of that, beginning in cell C16.  If you entered =SUBTOTAL(9,B2#), the result returned would be the sum of all the numeric values in the range B2:M15, and would occupy the single cell, B16.

To get around this, Microsoft introduced the BYCOL function that allows an array of values to be processed column-by-column.  But it relies on a nested LAMBDA function, since that allows the result to receive a value passed as an argument, once for each row in the referenced or nested array. and we can enter the following expression in B16: =BYROW(B2#,LAMBDA(eachCol,SUBTOTAL(9,eachCol)))

This will produce a row of results, occupying B16:M16, each of which is the sum of values for each row of the source array.  The BYCOL function calls the calculation nested as its second argument, once for each column, and passes a reference to the column of values in each column of the referenced array.  The calculation we have nested is a LAMBDA that defines one argument (called eachCol) and then simply passes that set of values through to the SUBTOTAL function, which adds them and passes the total back t o LAMBDA, which then passes them back to the BYCOL function, which is tabulating and outputting each of the results.

This extremely powerful, quite necessary if we are going to build properly dynamic models, but kind of a pain to keep typing in.  If only there were a SubtotalByColumn function that did this for us.  Well, using a lambda, we can do this!

Defined Name Settings

To create the lambda, simply use the define name feature in Excel to define this name in the workbook where it will be used.  The required settings are shown below.  Ctrl+Alt+F3 is the keyboard shortcut to open the New Name dialog, or you can select Formulas » Define Name.

Syntax of λSubtotalByColumn

=λSubtotalByColumn(func_num,array_to_subtotal)

where:
   func_num is a reference to a range, a nested expression that returns a value or a constant value that indicates what subtotalling function is to be used, as specified in the SUBTOTAL function: 1-11 and 101-111.

   array_to_subtotal is a reference to a range, a nested expression that returns an array or reference to a range, or a constant array whose members in each column are to be subtotalled.

Sample File

An example file, showing the definition and use of this lambda is available here: cllib.SubtotalByColumn.xlsx.

 Version History

¹  You may change the name of the lambda and drop the prefix.  The prefix ensures there is no clash with functions that may be added by Microsoft later or with lambdas from another source.  The names given here also tie-in to the GitHub sources for these functions from the Clarkson ITT LAMBDA Library which can be downloaded using the Advanced Formula Environment.