SumContainsλ() totals those row values where the row labels contain a unique letter, word or phrase..
In the above example, the Summary section's first row contains the description CAPEX. We see this word repeated in the Detail section's Description column. Our first formula that uses SumContainsλ selects the large values array (locks the reference), then the labels array (locks the reference), and then the word CAPEX. It then totals all those values in the large values array where the Detail Description contains the word CAPEX. We copy this formula down to create the summary totals we will need for our financial statements.
NOTE! When using functions that are intended to spill down, the best practice is to place totals at the top so the function's spill region never spills into the totals. This allows for the function's spill region to accommodate business changes without having to adjust the total formulas. If the spill region spills into other formulas, a #SPILL! error will make it obvious that we need to insert more lines between the spilling function above, and the formulas below.
NOTE! When using functions that create significant sized schedules that may expand after the initial model build, the best practice is to place that schedule on its own worksheet. This makes it unnecessary to insert new lines, ever.
SumContainsλ( ValuesToFilter, ValueLabels, FilterByText, [IgnoreCase?])
ValuesToFilter
(Required) An array of values to be filtered and totaled.
ValueLabels
(Required) An array containing the values' labels
FilterByText
(Required) A unique letter, word or phrase to find contained in ValueLabels
IgnoreCase?
(Optional) A flag to determine if Text's case is important. Default is TRUE.