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

Development Rationale

The purpose in developing this lambda is to simplify the derivation of horizontal subtotals from an array (most likely, a dynamic array).  Using the SUBTOTAL function and referring to an array of values will generate a subtotal for all cells.  It does not automatically process the array row-by-row as might be desired.  A similar problem arises for vertical subtotals and that is addressed in the separate λ Library 2.0: λSubtotalByColumn 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 column to the end of that, beginning in cell N2.  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, N2.

To get around this, Microsoft introduced the BYROW function that allows an array of values to be processed row-by-row.  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 N2: =BYROW(B2#,LAMBDA(eachRow,SUBTOTAL(9,eachRow)))

This will produce a column of results, occupying N2:N15, each of which is the sum of values for each row of the source array.  The BYROW function calls the calculation nested as its second argument, once for each row, and passes a reference to the row of values in each row of the referenced array.  The calculation we have nested is a LAMBDA that defines one argument (called eachRow) 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 BYROW 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 SubtotalByRow 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 Lambda

=cllib.SubtotalByRow(funcNum,arrayToSubtotal)

where:

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

   arrayToSubtotal 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 row are to be subtotalled.

Sample File

An example file, showing the definition and use of this lambda is available here: cllib.SubtotalByRow.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.