BYCOL can accept an abbreviated “eta lamba” syntax for simple operations like SUM, as explained below.
The short form is for convenience.
It is concise and easy to read.
However, the behavior cannot be customized.
The long-form syntax uses the LAMBDA function and can be customized as desired.
See below for an example of BYCOL formulas that use both options.
However, you are free to use whatever name you like.
It will not work in all scenarios because the logic can’t be modified.
For example, the formula in the next section below can’t use the eta lambda syntax.
The formula is a bit more complex because we need more logic.
This is a situation where we can’t use the short-form syntax above.
SeeBoolean operations in array formulasfor more information about the logic inside of SUM.
You could use COUNTIFS instead of SUM to solve this problem.
The trick is to wrap the functions in theHSTACKorVSTACKfunctions (depending on the layout needed).
Because of the currentarray of arrayslimitation in Excel, this formula will not spill down across all columns.
It will initially only calculate results for the first column.
You will then need to copy the formula manually to all columns in a second step.
LET Function
The Excel LET function lets you define named variables in a formula.
BYROW can apply stock functions like SUM, COUNT, and AVERAGE or a custom LAMBDA function.
All results are returned at the same time in a single array….
