BYROW 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 BYROW 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.
SeeBoolean operations in array formulasfor more information about the logic inside of SUM.
You could use COUNTIFS instead of SUM to solve this problem.
However, COUNTIFSrequires a range, which means you could’t give BYROW anarrayif you use COUNTIFS in the calculation.
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 rows.
It will initially only calculate results for the first row.
You will then need to copy the formula manually to all rows in a second step.
LET Function
The Excel LET function lets you define named variables in a formula.
BYCOL 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….
